CODEWITHSUNDEEP
c#sql-serverentity-framework
Michael Witt
Michael Witt

Reputation: 1712

Use SqlQuery in EntityFramework to compose a real IQueryable

Is there a way to get SqlQuery or any other sql execution method to compose a model object and allow methods like Include to work? I'm doing this (where view_products is an SQL table function):

var p = context.SqlQuery<Product>("select * from view_products(@param1)", new SqlParameter("param1", "somevalue"));

I want to then do this:

var list = p.Include(i => i.Inventories).ToList();

This gets me a DbRawSqlQuery. This I can't "Include", because you can't do that on a DbRawSqlQuery. So, I add an AsQueryable to the end, like:

var p = context.SqlQuery<Product>("select * from view_products(@param1)", new SqlParameter("param1", "somevalue")).AsQueryable<Product>();

But, I understand this is really giving me an IEnumerable, so the .Include doesn't yield any results.

I'm looking for suggestions on another way to do this where I get my Products from the SQL function and then load in their related Inventory objects so that I have an object graph. Thanks!

Upvotes: 1

Views: 1974

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109281

SqlQuery poses problems some of which I wasn't yet aware of.

No Include

You correctly explain why Include doesn't have any effect after you convert an IEnumerable to an IQueryable. Include extends an expression, and an IEnumerable doesn't have an expression (and doesn't magically get one after converting it to IQueryable).

No lazy loading without attaching

So you end up having a list of Products not attached to the context and not capable of lazy loading. You have to attach the Products to the context explicitly (e.g. by setting their state to Unchanged) to make them lazy loading.

This is something I just found out and which is odd. The entities are materialized as proxy objects, but, contrary to detached entities that are fetched by DbSet.AsNoTracking(), they don't display lazy loading. I think it's a bug and I reported it.

But lazy loading causes the n + 1 query anti-pattern: queries that fetch Inventory objects for each Product separately. So it's not the best way to get your object graph.

No relationship fixup

So what you really want is load all Inventory objects that belong to these products in one shot and let relationship fixup do its job (i.e. EF auto-fills all Product.Inventories collections):

context.Inventories.Where(i => productIds.Contains(i.ProductId)).Load();

...where productIds is a list of procuct Ids in p.

But relationship fixup only works for attached entities, so again, you'll have to change their state to Unchanged explicitly. If you do that, you're ready to go... except you aren't.

Again there's a difference with detached entities fetched by DbSet.AsNoTracking(). The latter respond to relationship fixup, the ones from SqlQuery don't!

(Rather) elaborate solution

So what's left is: load the related Inventory objects as show above and fill the Product.Inventories collections yourself:

var query = from product in p
            join inv context.Inventories.Local
                on product.ProductId equals inv.ProductId
                into inventories
            select new { product, inventories }
foreach(var anon in query)
{
    anon.product.Inventories = anon.inventories.ToList();
}

Of course this is all quite unsatisfactory.

Upvotes: 1

Related Questions