0

I want to ignore null when the query check the value exist in database table in C# using LINQ and Lambda expression.

Below line is checking null as well. When it finds null then ignore it.

var nmcExist = db.AspNetUsers.Any(a => a.NMC_Number== model.NMC_Number);
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
KeentoLearn
  • 355
  • 1
  • 4
  • 12
  • 2
    `.Any(a => a.NMC_Number != null && a.NMC_Number == model.NMC_Number);`? – spender Feb 27 '20 at 09:45
  • What does `is checking null as well` mean? And which ORM are you actually using? LINQ is just a query language that the ORM will map to a query. Different ORMs, even different EF versions may produce different SQL queries. In this case the *ONLY* way to check for `null` would be to emit `a.NMC_Number is @p1 or a.NMC_NUMBER IS Null`. EF *doesn't* do that by default, but that behavior can change – Panagiotis Kanavos Feb 27 '20 at 09:46
  • So, **Before** 6.2, the default behavior was to *not* emit `IS NULL`, controlled by [UseCSharpNullComparisonBehavior](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.core.objects.objectcontextoptions.usecsharpnullcomparisonbehavior), which is `false` by default. This [changed in 6.2](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.infrastructure.dbcontextconfiguration.usedatabasenullsemantics) and the new default *does* generate `IS NULL`. EF Core doesn't have the option yet. So *which* ORM are you using, which version? – Panagiotis Kanavos Feb 27 '20 at 09:56
  • The EF Core option is [UseRelationalNulls](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.infrastructure.relationaloptionsextension.userelationalnulls?view=efcore-3.1). – Panagiotis Kanavos Feb 27 '20 at 10:03

1 Answers1

1

You might be looking for 2 options:

  • If null, return true
.Any(a => a.NMC_Number == null || a.NMC_Number == model.NMC_Number);
  • If is not null, return true then check the second condition.
.Any(a => a.NMC_Number != null && a.NMC_Number == model.NMC_Number);

Updated

As @Panagiotis Kanavos 's comment: It really depends on:

  • Which ORM are you actually using?
  • Which version?

This is simply because, Different ORMs, even different EF versions may produce different SQL queries. Besides, Before EF v6.2, the default behavior was to not emit IS NULL.

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
  • 1
    I think he is asking for null to be ignored – Peter Smith Feb 27 '20 at 09:55
  • I've updated my answer with 2 options. Please take a look at. Many thanks. – Nguyễn Văn Phong Feb 27 '20 at 09:59
  • 1
    There may not be any need for this. In EF 6.2, one only needs to set [UseDatabaseNullSemantics](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.infrastructure.dbcontextconfiguration.usedatabasenullsemantics?view=entity-framework-6.2.0) to `true`. In EF Core 3.1, it's [UseRelationalNulls](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.infrastructure.relationaloptionsextension.userelationalnulls?view=efcore-3.1) – Panagiotis Kanavos Feb 27 '20 at 10:01
  • Yes, sir. I've just updated my answer with your advice. Many thanks to @ Panagiotis Kanavos – Nguyễn Văn Phong Feb 28 '20 at 01:41