Why does this additional connection increase the number of requests?

Question

I'm having problems with efficient LINQ-to-SQL parsing. I am trying to do something like this:

from x in Items select new { Name = x.Name TypeARelated = from r in x.Related where r.Type == "A" select r } 

As expected, it creates one query from the "Items" table, and the left join - in the "Related" table. Now, if I add some more similar lines ...

 from x in Items select new { Name = x.Name TypeARelated = from r in x.Related where r.Type == "A" select r, TypeBRelated = from r in x.Related where r.Type == "B" select r } 

As a result, a similar query is made to the first attempt, and then a separate query to the "Compliance" table for each entry in the "Elements". Is there a way to wrap this all in one request? What would be the reason for this? Thanks in advance for any help you can provide.

Answer 1

The above query, if it is written directly in SQL, will be written like this (pseudocode):

 SELECT X.NAME AS NAME, (CASE R.TYPE WHEN A THEN R ELSE NULL) AS TypeARelated, (CASE R.TYPE WHEN B THEN R ELSE NULL) AS TypeBRelated FROM Items AS X JOIN Related AS R ON <some field> 

However, linq-to-sql is not so efficient, from your explanation, it is combined, and then it goes to individually compare each record. A better way would be to use two linq queries, similar to your first example, which will generate two SQL queries. Then use the result of the two linq queries and attach them to them, which will not generate the SQL query. This method would limit the number of queries executed in SQL to 2.

If the number of conditions ierType == "A", etc. over time, various conditions will increase or various conditions will be added; you are better off using a stored procedure, which will be a single SQL query at any time.

Hasanain

Answer 2

You can use active boot to make one connection on the server to see if this helps. Try it.

 using (MyDataContext context = new MyDataContext()) { DataLoadOptions options = new DataLoadOptions(); options.LoadWith<Item>(i => i.Related); context.LoadOptions = options; // Do your query now. }