I have a working method-based query that looks like this:

string param = request.QRBarang.Split('~')[1];
var a = await _db.A.FirstOrDefaultAsync(a => a.IdA == param);
var b = await _db.B.FirstOrDefaultAsync(b => b.IdB1 == a.IdA);
var c = await _db.C.FirstOrDefaultAsync(c => c.IdC1 == b.IdB2);
var d = c != null
         ? await _db.D.FirstOrDefaultAsync(d => d.IdD == c.IdC2)
         : null;
var dto = new DTOFin { ...};

I'm still learning LINQ and trying to convert the syntax into query expression, but always get error when the value of c is null. Here's my try:

var dto = (from a in _db.A
           join b in _db.B on a.IdA equals b.IdB1
           join c in _db.C on b.IdB2 equals c.IdC1
           join d in _db.D on c.IdC2 equals d.IdD
           where a.IdA == param && object.Equals(c.IdC2, d.IdD)
           select new DTOFin { ...}).FirstOrDefaultAsync();

I also tried using join c in _db.C on b.IdB2 equals c?.IdC1 but produced

Error CS8072 An expression tree lambda may not contain a null propagating operator.

How should I write the first syntax equivalent in query expression?

CodePudding user response：

Assuming you have Navigation Properties configured correctly then this should work:

(I note that EF6 and EF Core's scaffolding templates (for auto-generating entity classes from an existing database) do this for you already.)

(Also, I really don't like C#'s keyword-style Linq expressions because in order to do almost anything you need to tack-on Linq extension-methods, which clashes aesthetically with keyword-style expressions. I can't think of any good reason to use keyword-style Linq expressions thesedays, honestly).

I assume _db is your DbContext.

using System.Linq;
using System.Data.Entity; // for EF6
using Microsoft.EntityFrameworkCore.Query; // for EF Core

A a = await _db.A
    .Include( a => a.B )
    .Include( a => a.B.C )
    .Include( a => a.B.C.D )
    .Where( a => a.IdA == param )
    .SingleOrDefaultAsync();

// b, c, and d can then be gotten from A:
B b = a.B;
C c = a.B.C;
D d = a.B.C?.D; // It looks like A.B and B.C are INNER JOIN while C.D is LEFT OUTER JOIN. Linq will use the correct JOIN type (INNER, LEFT, etc) based on your model configuration. You cannot tell from the query alone.

return new DTOFin() { ... };

If you don't have navigation properties set-up (and you should...) then you can do Joins manually - but it is noticably more gnarly because Linq's .Join method was never intended to be used directly because you're expected to use Navigation Properties instead.

• Note that because this Linq query is being used with Entity Framework it means your query must be representable in SQL...
  • Which it means that certain limitations apply: such as not using the ?. operator - which is your issue.
  • Other limitations include not being able to use your own custom predicate functions (unless they're also Expression<Func<>>) because you can't just put C# code into a SQL query.

I believe the below query should work, but I cannot say for certain without knowing more about your EF model configuration and database design - you haven't provided enough detail in your opening question post.

var a_b_c_d = await _db.A
    .Join( _db.B, a     => a.IdA       , b => b.IdB1, ( a    , b ) => new { a, b } )
    .Join( _db.C, a_b   => a_b.b.IdB2  , c => c.IdC1, ( a_b  , c ) => new { a_b.a, a_b.b, c } )
    .Join( _db.D, a_b_c => a_b_c.c.IdC2, d => d.IdD , ( a_b_c, d ) => new { a_b_c.a, a_b_c.b, a_b_c.c, d } )
    .Where( a_b_c_d => a_b_c_d.a.IdA == param )
    .SingleOrDefaultAsync();

A a = a_b_c_d.a;
B b = a_b_c_d.b;
C c = a_b_c_d.c;
D d = a_b_c_d.d;

return new DTOFin() { ... };

CodePudding user response：

I think this will work by using LINQ query syntax.

    var dto = (from a in _db.A
               join b in _db.B on a.IdA equals b.IdB1
               join c in _db.C on b.IdB2 equals c.IdC1 into cTemp
               from cT in cTemp.DefaultIfEmpty()
               join d in _db.D on c.IdC2 equals d.IdD into dTemp
               from dT in dTemp.DefaultIfEmpty()
               where a.IdA == param
               select new DTOFin 
               {
               ...
               }).FirstOrDefaultAsync();

you can select c and d table values from cT and dT variables.