Optimizing Linq Query to minmal fetching time?

Question

I have Nested Conditional Operator Linq query to fetch records. Problem is as i've written multiple select&join on each conditional db hitting time is too high to fetch records and I need to reduce the no.of time query hitting db.

As I'm new to Enity Framwork i don't much optimizing way to use conditonal statements.

var uniqID = 12;
var keyID = 439;

var result = (from t1 in Table1
where t1.uniqueID == uniqID && t1.column9 == keyID && t1.column11 == true
select new {
  Data1 = t1.Column1,
  Data2 = t1.Column2,
  Data3 = t1.Column3 == true ? (from t2 in Table2
  where t2.uniqueID == uniqID && t2.Id == t1.column10
  select t2.Data4).FirstOrDefault() : (from t3 in Table3
  join t4 in Table4 on new {
    t3.uniqueID,
    t3.coulmn6
  }
  equals new {
    t4.uniqueID,
    t4.coulmn6
  }
  where t3.uniqueID == uniqID && t3.keyID == t1.column10
  select t4.Data4).FirstOrDefault(),
  ReceiptNo = (from t3 in Table3
  join t5 in Table5 on new {
    t3.uniqueID,
    t3.coulmn6
  }
  equals new {
    t5.uniqueID,
    t5.coulmn6
  }
  where t3.uniqueID == uniqID && t3.keyID == t1.column9
  select t5.Data5).FirstOrDefault()
}).Dump();

This is similar one . Like This i've to add few(say 4-5) more subqueries along with above one

In EF you should almost never user a JOIN. Instead your entities should have Navigation Properties, and your queries should use those. https://learn.microsoft.com/en-us/ef/ef6/fundamentals/relationships — David Browne - Microsoft, Nov 21 '20 at 16:36

score 0 · Answer 1 · answered Nov 21 '20 at 17:16

I hope it will be translated by EF. Idea is to do OUTER APPLY for both subqueries.

var uniqID = 12;
var keyID = 439;

var query = 
   from t1 in Table1
   where t1.uniqueID == uniqID && t1.column9 == keyID && t1.column11 == true
   from t2Data in Table2.Where(t2 => t2.uniqueID == uniqID && t2.Id == t1.column10)
      .Select(x => x.Data4).Take(1).DefaultIfEmpty()
   from t3Data in (
      from t3 in Table3
      join t4 in Table4 on new {
         t3.uniqueID,
         t3.coulmn6
      }
      equals new {
         t4.uniqueID,
         t4.coulmn6
      }
      where t3.uniqueID == uniqID && t3.keyID == t1.column10
      select t4.Data4).Take(1).DefaultIfEmpty()

   select new {
      Data1 = t1.Column1,
      Data2 = t1.Column2,
      Data3 = t1.Column3 == true ? t2Data : t3Data
   };

query.Dump();

Optimizing Linq Query to minmal fetching time?

1 Answers1

Linked