M:N Prefetch + Generic method taking Prefetch as an argument

Posts   
 
    
Posts: 37
Joined: 09-Nov-2016
# Posted on: 08-Dec-2016 13:12:54   

Hi,

I am creating a Web API using ASP.NET Core Web Application (using the .NET Framework) with Database First. I am using LLBLGen Pro version 5.1 and the LLBLGen Runtime.

I have two questions:

1: I have two tables with a many-to-many relationship, Item and Collection which are related through the ItemCollection table and I am trying to create a list of all my items including the names of all associated Collections (which is a property in my Collection table). I have read about the prefetch paths and using the below code:


var path = new PrefetchPath2(EntityType.ItemEntity);
path.Add(ItemEntity.PrefetchPathItemCollections);

using (DataAccessAdapter adapter = new DataAccessAdapter(“ConnectionString”))
{
    var data = new LinqMetaData(adapter);

    return data.Item.Select(all => all).WithPath(path).ToList();
}

This gets me the ids of an Item's associated Collections, but not the Collection object itself. This means that I have to match the Collection id with the Collection table in a separate query in order to get the Collection name, like so:

//Get all items include relationship tables (items)
//Get all items from the collection table in (collections)

foreach (ItemEntity i in items)
{
    foreach (ItemCollectionEntity ice in i.ItemCollections)
    {
        var name = collections.Where(x => x.Id == ice.CollectionId).Select(y => y.Name).First());
    }
}

Compared to the same in Entity Framework:

//Get all items include collections

foreach (Item i in items)
{
    foreach (Collection c in i.collections)
    {
        var name = c.Name;
    }
}

It is not a big deal, I am just sure I am missing something?

2: In order to reduce the amount of overhead and make the code easier to unit-test, I am trying to make a generic version of the most common methods, which is GetAll, Find, Post, Put and Delete (inspired by https://goo.gl/4DnImB). However, I have run into a problem with a generic get method (I am new to generics) where I can specify the prefetch paths as an argument (using the knowledge from the first question).

What I am trying to do is something like this (pseudo-code):

public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
{
    public IEnumerable<TEntity> GetAllWithPrefetch(List<string> prefetches)
    {
        var path = new PrefetchPath2(EntityType.TEntity);
        path.Add(prefetches);

        using (DataAccessAdapter adapter = new DataAccessAdapter("ConnectionString"))
        {
            var data = new LinqMetaData(adapter);

            return data.GetQueryableForEntity<TEntity>().Select(all => all).WithPath(path).ToList();
        }
    }
}

However the PrefetchPath2 takes an EntityType enum which is specific to the object type and there seem to be no way to call WithPath on the GetQueryableForEntity() so I am quite stuck. Do you have some pointers on how to achieve the above?

Best regards Andreas

Edited for clarity

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 08-Dec-2016 20:32:58   

1- If you have a m:n relation, you would have a navigator for the end of the relation (e.g. Product.OrderCollectionViaOrderDetails) which you can fetch via prefetchPath as well. This should give you the end collection you are after, n one fetch.

2- The Entity has a property LLBLGenProEntityTypeValue which you can use.

Posts: 37
Joined: 09-Nov-2016
# Posted on: 09-Dec-2016 13:44:33   

Hi Walaa,

Thank you for your response.

1: Thank you. I am using Database First, and the specific m:n navigation with the "Via" navigation, wasn't there. I created a m:n relationship in the designer, got the "Via" navigation and now everything is working. Just need to remember to do this manually.

2: I am not sure I know how to use this. I was able to get the PrefetchPath2 initialized using this:

var path = new PrefetchPath2((int)Enum.Parse(typeof(EntityType), typeof(T).Name))

But I still have two issues. As I wrote, I am new to generics so I hope the following make a little sense (and at this point I am not even sure my approach makes sense):

A: In order to take a string (fx “PrefetchPathSomeCollectionViaSomeObject”) and add this to the path, I need to initialize a PrefetchPathElement2 (https://goo.gl/VTBpys), but I am not sure how to initialize this, as this takes some arguments which I don’t seem to be able to infer from T. Is this the correct way to do this or is there some other way to get a string added to a prefetch search?

B: If I get the above initialized, I need to be able to add the WithPath parameter to the search. However, WithPath requires an IQueryable<IEntityCore> and I have IQueryable<T>. The following satisfies the compiler but throws an error (Can't obtain entity factory for type 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityCore') when I use it:

public IEnumerable<T> GetAllWithPrefetch(List<string> prefetches)
{
    using (DataAccessAdapter adapter = new DataAccessAdapter(Variables.GetConnectionString(catalog)))
    {
        var data = new LinqMetaData(adapter);

        var queryable = (IQueryable<IEntityCore>) data.GetQueryableForEntity<T>();

        return queryable.Select(all => all).WithPath(path).ToList() as IEnumerable<T>;
    }
}

The above seems overly complex, so I am not sure that I am on the right path. I can see other people have tried implementing the repository pattern, but I am not able to find any who succeeded.

Best regards Andreas

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 10-Dec-2016 15:55:54   

Jordbaerhjelmen wrote:

1: Thank you. I am using Database First, and the specific m:n navigation with the "Via" navigation, wasn't there. I created a m:n relationship in the designer, got the "Via" navigation and now everything is working. Just need to remember to do this manually.

There is a setting for this at Project Settings->Reverse Engineering->Auto add many to many relationships (ref...) :

Docs wrote:

When set to true, the designer will automatically add new m:n relationships it detects during the reverse engineering process of entities from relational model data. Default is false.

David Elizondo | LLBLGen Support Team
Posts: 37
Joined: 09-Nov-2016
# Posted on: 13-Dec-2016 13:10:06   

Ahh thank you.

Should I move the second question to the architecture forum and close this one?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Dec-2016 03:33:38   

Yes please, I'll close this one for you.