Dynamic Include statements for eager loading in a query - EF 4.3.1

Question

I have this method:

public CampaignCreative GetCampaignCreativeById(int id) { using (var db = GetContext()) { return db.CampaignCreatives .Include("Placement") .Include("CreativeType") .Include("Campaign") .Include("Campaign.Handshake") .Include("Campaign.Handshake.Agency") .Include("Campaign.Product") .AsNoTracking() .Where(x => x.Id.Equals(id)).FirstOrDefault(); } }

I would like to make the list of Includes dynamic. I tried:

public CampaignCreative GetCampaignCreativeById(int id, string[] includes) { using (var db = GetContext()) { var query = db.CampaignCreatives; foreach (string include in includes) { query = query.Include(include); } return query.AsNoTracking() .Where(x => x.Id.Equals(id)).FirstOrDefault(); } }

But it didn't compile. I got this error:

Cannot implicitly convert type 'System.Data.Entity.Infrastructure.DbQuery' to 'System.Data.Entity.DbSet'. An explicit conversion exists (are you missing a cast?)

Does anyone know how to make the list of Includes dynamic?

Thanks

I did a plugin that do just that here is the link codeproject.com/Tips/1205294/… — Alen.Toma
– Alen.Toma, Commented Sep 11, 2017 at 16:48

Mikael Östberg · Accepted Answer · 2013-10-04 11:09:16Z

I am more fond of the non-string expressive way of defining includes. Mainly because it doesn't rely on magic strings.

For the example code, it would look something like this:

public CampaignCreative GetCampaignCreativeById(int id) { using (var db = GetContext()) { return db.CampaignCreatives .Include(cc => cc.Placement) .Include(cc => cc.CreativeType) .Include(cc => cc.Campaign.Select(c => c.Handshake.Select(h => h.Agency))) .Include(cc => cc.Campaign.Select(c => c.Product) .AsNoTracking() .Where(x => x.Id.Equals(id)) .FirstOrDefault(); } }

And to make those dynamic, this is how you do that:

public CampaignCreative GetCampaignCreativeById( int id, params Expression<Func<T, object>>[] includes ) { using (var db = GetContext()) { var query = db.CampaignCreatives; return includes .Aggregate( query.AsQueryable(), (current, include) => current.Include(include) ) .FirstOrDefault(e => e.Id == id); } }

Which is used like this:

var c = dataService.GetCampaignCreativeById( 1, cc => cc.Placement, cc => cc.CreativeType, cc => cc.Campaign.Select(c => c.Handshake.Select(h => h.Agency)), cc => cc.Campaign.Select(c => c.Product );

I said it above but I'll say it here in case anyone missed it: Some projects you will get a compile error with this solution. Add using System.Data.Entity; to access the Include extension method on IQueryable.
This answer should've been the one checked up. I found it extremely useful when it comes to EF strongly typed navigation properties on generic types.
But when you see transformed SQL for this LINQ, the above includes always shows inner joins. Is there a way you could achive left or right joins using the same method of includes. For reference I'm including my post link stackoverflow.com/questions/55129505/… Thanks.

Darin Dimitrov · Accepted Answer · 2012-04-03 20:46:49Z

20

Make the query variable queryable:

public CampaignCreative GetCampaignCreativeById(int id, string[] includes) { using (var db = GetContext()) { var query = db.CampaignCreatives.AsQueryable(); foreach (string include in includes) { query = query.Include(include); } return query .AsNoTracking() .Where(x => x.Id.Equals(id)) .FirstOrDefault(); } }

answered Apr 3, 2012 at 20:46

Darin Dimitrov

1.0m276 gold badges3.3k silver badges3k bronze badges

5 Comments

dudeNumber4 Over a year ago

I don't get it. IQueryable has no Include method. This doesn't compile.

erstaples Over a year ago

Some projects you will get a compile error with this solution. Add using System.Data.Entity; to access the Include extension method on IQueryable

Davut Gürbüz Over a year ago

This does not work For EF4.0. Missing Include extension

Davut Gürbüz Over a year ago

IQuerable does not have Include extension for EF4.0. For EF4.0 you oppositely cast IQuerable<T> to ObjectQuery<T>. Then you can include details.

Vasil Valchev Over a year ago

does someone have code snipped for nested includes?

Slauma · Accepted Answer · 2012-04-03 22:27:25Z

Giving the compiler a hint by using IQueryable<CampaignCreative> instead of var will work too.

IQueryable<CampaignCreative> query = db.CampaignCreatives; // or DbQuery<CampaignCreative> query = db.CampaignCreatives;

When using var the compiler infers DbSet<T> for query which is more specific than the type returned by Include (which is DbQuery<T> (=base class of DbSet<T>) implementing IQueryable<T>), so you can't assign the result to the query variable anymore. Hence the compiler error on the query = query.Include(include) line.

Nicolò Rebaioli · Accepted Answer · 2020-02-14 21:31:51Z

I wrote this method to retrieve any set of entity dynamically based on their types. I used the IDbEntity interface to provide a valid key to search the userId in all the classes. The Util.GetInverseProperties<T>() method is used to get the properties needed in the Include statement.

public IEnumerable<T> GetItems<T>(string userId) where T : class, IDbEntity { var query = db.Set<T>().Where(l => l.UserId==userId); var props = Util.GetInverseProperties<T>(); foreach (var include in props) query = query.Include(include.Name); return query .AsNoTracking() .ToList(); }

public interface IDbEntity { public string UserId { get; set; } }

public static List<PropertyInfo> GetInverseProperties<T>() { return typeof(T) .GetProperties() .Where(p => Attribute.IsDefined(p, typeof(InversePropertyAttribute))) .ToList(); }

Collectives™ on Stack Overflow

Dynamic Include statements for eager loading in a query - EF 4.3.1

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Linked

Related