Using Isnull or Select Coalesce in Linq..

IsNull or Coalesce functionality in LINQ?

Try refactoring the IsNullOrEmpty condition like this:

return (from r in Repository.Query<Measurement>()
    where
        (string.IsNullOrEmpty(postalCode)
            || r.Postal.ToLowerInvariant() == postalCode.ToLowerInvariant()
        )
        &&
        (string.IsNullOrEmpty(trait)
            || r.Trait.ToLowerInvariant() == trait.ToLowerInvariant()
        )
    select r).ToList();

That may cause LINQ to evaluate the IsNullOrEmpty before sending off the query. If not, you could precalculate them manually and put a couple boolean variables in their place.

null-coalescing operator ?? throws an error in LINQ

Your current query:

Single? cost = _context
    .Orders
    .Where( r => r.OrdId == 123 )
    .Select( t => t.cost ?? 0 )
    .SingleOrDefault();

Can be rewritten as:

Single? cost = _context.
    .Orders
    .SingleOrDefault( r => r.OrdId == 123 )
    ?.Cost;

cost will be null if there are no rows where r.OrderId == 123 or if r.Cost IS NULL (SQL NULL). Note my use of the ?. operator before .Cost.

Equivalent of SQL ISNULL in LINQ?

Since aa is the set/object that might be null, can you check aa == null ?

(aa / xx might be interchangeable (a typo in the question); the original question talks about xx but only defines aa)

i.e.

select new {
    AssetID = x.AssetID,
    Status = aa == null ? (bool?)null : aa.Online; // a Nullable<bool>
}

or if you want the default to be false (not null):

select new {
    AssetID = x.AssetID,
    Status = aa == null ? false : aa.Online;
}

---

Update; in response to the downvote, I've investigated more... the fact is, this is the right approach! Here's an example on Northwind:

        using(var ctx = new DataClasses1DataContext())
        {
            ctx.Log = Console.Out;
            var qry = from boss in ctx.Employees
                      join grunt in ctx.Employees
                          on boss.EmployeeID equals grunt.ReportsTo into tree
                      from tmp in tree.DefaultIfEmpty()
                      select new
                             {
                                 ID = boss.EmployeeID,
                                 Name = tmp == null ? "" : tmp.FirstName
                        };
            foreach(var row in qry)
            {
                Console.WriteLine("{0}: {1}", row.ID, row.Name);
            }
        }

And here's the TSQL - pretty much what we want (it isn't ISNULL, but it is close enough):

SELECT [t0].[EmployeeID] AS [ID],
    (CASE
        WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(10),@p0)
        ELSE [t2].[FirstName]
     END) AS [Name]
FROM [dbo].[Employees] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[FirstName], [t1].[ReportsTo]
    FROM [dbo].[Employees] AS [t1]
    ) AS [t2] ON ([t0].[EmployeeID]) = [t2].[ReportsTo]
-- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

QED?

Null coalesce not working in LINQ query

In SQL, NULL is not equal to NULL.

You can interpret NULL as meaning: "there is value, but I don't know what it is". So if you're comparing two NULL values, you're really asking "is the first unknown value equal to the second unknown value?" Of course, there is no reason to assume they are, so SQL will say "no".

I am assuming that this is causing your problem. You can verify that by looking at the actual SQL produced. If it's using the SQL = operator, this is indeed the problem. You can verify that by running the SQL in a database tool, such as SQL Management Studio in case you're using SQL Server.

UPDATE:

The condition

([Extent5].[MediaCategoryFormatID] = [Extent5].[MediaCategoryFormatID]) 

will indeed return false when [Extent5].[MediaCategoryFormatID] is NULL.

That answers the question "Why is it returning nothing?"

However, another question come to mind: why would the entity framework generate that SQL from this linq query?

I'm afraid that linq to entities is not exactly known for the quality of its SQL generation, and this case seems to confirm that. You might consider Linq to SQL. Even if that seems to be a dead-end track in the long run, the current implementation if a lot better than linq to entities.

In either case, have you tried something like

someObjectList.Where(x => 
    !item1.hasValue || 
    x.SomeItem1.HasValue && x.SomeItem1.Value == item1.Value)

Make sure to verify that under the profiler as well though, linq to entities might mess it up too.

c# linq sql coalesce conversion

I've not tried it, but could you try something like:

var dt = new DateTime(1753, 1,1);
query = query.Where(v =>  (v.a_end_dt ?? v.a_start_dt ?? dt) >= aDateMin);

Linq to SQL query with COALESCE in where clause

Try this:

var positions = (from a in dbContext.TABLE1 join b in dbContext.TABLE2
                   on a.LocationLoadPositionId equals b.FkLocationLoadPositionId into c from d in c.DefaultIfEmpty()
                     where d.UploadStatus == false || d == null select new { a, d }).ToList();

Linq to Sql isnull in where clause not working

just use the coalesce ?? operator

where

(tesrt.EH_PP_TESRT_TeacherEvalStatusID  ?? 0) == 1

or if it's a string (not really clear with your code)

(tesrt.EH_PP_TESRT_TeacherEvalStatusID  ?? "0") == "1"

But with the sample code given, this seem to be useless (the first version should do the same).

If what you mean is

take all values where tesrt.EH_PP_TESRT_TeacherEvalStatusID IS NULL or
tesrt.EH_PP_TESRT_TeacherEvalStatusID == 1

then

tesrt.EH_PP_TESRT_TeacherEvalStatusID == null || tesrt.EH_PP_TESRT_TeacherEvalStatusID == 1

or

tesrt.EH_PP_TESRT_TeacherEvalStatusID ?? 1 == 1

---

Related Topics