Entity Framework Core2 LINQ - each join runs as a separate query

asp.net-core asp.net-mvc c# entity-framework-core

Question

I have something like this in my C# MVC controller:

from table1 in db.Table1.AsQueryable()
join table2 in db.Table2.AsQueryable() on table1.Col1 equals table2.Col1
join table3 in db.table3.AsQueryable() on new { table2.Col2, table2.Col5 } equals new { table3.Col2, table3.Col5 }
.
.
few more joins
.
.
WHERE ......
select new {table1.Prop1, table2.Prop2, table3.Prop3}

When I watch what it runs on SQL profiler, I was expecting a single query with all the joins. What it does instead, it selects all columns from all tables in separate queries. i.e. Runs

SELECT * FROM Table2 --Instead of * it has all column names

when that's finished running, it runs

SELECT * FROM Table3 --Instead of * it has all column names

and so on for each table. Tables are big so it takes too long, using a lot of memory. I added AsQueryable() on the entities but it didn't make a difference, still multiple queries. db is a DbContext, using core 2.

How can I change the LINQ or some other setting so the whole thing runs as a single query?

Update

It looks like the problem was caused by having Convert.ToInt32( on one of the join columns. The int column I was joining on is nullable in one table and non-nullable in the other table, I had Convert.ToInt32( on the nullable table, removing the convert generated a single query.

1
0
8/17/2017 1:58:22 AM

Popular Answer

According to LINQ2SQL documents:

When you query for an object, you actually retrieve only the object you requested. The related objects are not automatically fetched at the same time.

The DataLoadOptions class provides two methods to achieve immediate loading of specified related data. The LoadWith method allows for immediate loading of data related to the main target. The AssociateWith method allows for filtering related objects.

This is an issue with Lazy Loading vs Eager Loading.

This is a great post with very good explanation.

Lazy Loading And Eager Loading In LINQ To SQL

0
8/17/2017 1:45:56 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow