Null check in Linq query expression

Question

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?

Your also need to await all Async Linq methods. Entity Framework does not support concurrent queries, so your code should be like var a = await _db.A.FirstOrDefaultAsync(a => a.IdA == param);. — Dai
– Dai, Commented Sep 1, 2021 at 1:50
I assume this is EF Core? Do you not have any navigation properties in your model? — Jeremy Lakeman
– Jeremy Lakeman, Commented Sep 1, 2021 at 5:29
Agree with Jeremy - you do seem to be making your life awfully hard work compared to being able to say eg context.Employees.Where(e => e.Department.Manager.LengthOfService > 10) to find all employees working for a manager who has served longer than 10 years — Caius Jard
– Caius Jard, Commented Sep 1, 2021 at 6:50
I will need to take a look into that first, @JeremyLakeman... It's a query to get the history of a Stock Keeping Unit and track its current location. I have to get the data from various tables. I didn't design the database, I'm the UI guy that for some reasons have to learn the backend programming by himself. — Billy Joseph
– Billy Joseph, Commented Sep 1, 2021 at 8:21

Dai · Accepted Answer · 2021-09-02 10:39:42Z

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() { ... };

Need to learn lots before I completely figure this out, but I think you gave me enough to start with.

TiginMannuel · Accepted Answer · 2021-09-04 19:04:07Z

0

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.

answered Sep 4, 2021 at 19:04

TiginMannuel

295 bronze badges

Collectives™ on Stack Overflow

Null check in Linq query expression

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related