Entity Framework Core LINQ query with multiple where clauses

Question

I need some sample code for my project. I am working with EF CORE 2.1.1.

I created a function which accepts 3 parameters, inside that function I am executing a LINQ (lambda expression query), returning a list.

Here is my function :

public IEnumerable<Donneesource> GetAllSourcesFromBDD(DateTime PremierDateCom, DateTime DerniereDateCom, string Secteur) { try { //Récupération des données. IEnumerable<Donneesource> RawDatas = _sourceDAL.Donneesource .Where(ic => ic.EstCopieDestination == false) .Where(s => PremierDateCom != DateTime.MinValue && s.SComPremierDate.Value.Date == PremierDateCom.Date) .Where(s => DerniereDateCom != DateTime.MinValue && s.SComDerniereDate.Value.Date == DerniereDateCom.Date) .Where(s => Secteur != string.Empty && s.SSectNom == Secteur) .ToList(); return RawDatas; } catch(Exception) { return null; } }

By default I set DateTime params to DateTime.MinValue (PremierDateCom,DerniereDateCom) and string param to string.Empty (Secteur).

I am trying to create a single query with where clauses. I want to ignore the where clauses with default params. For example If PremierDateCom = DateTime.MinValue (or other params) then I want to ignore the where clauses if something I want to include my where clause in my query.

I don't want to create a query like this:

 //Filtre if (PremierDateCom != DateTime.MinValue) RawDatas = RawDatas.Where(x => x.SComPremierDate.Value.ToShortDateString() == PremierDateCom.ToShortDateString()); //Filtre if (DerniereDateCom != DateTime.MinValue) RawDatas = RawDatas.Where(x => x.SComDerniereDate.Value.ToShortDateString() == DerniereDateCom.ToShortDateString()); //Filtre if (Secteur != null) RawDatas = RawDatas.Where(x => x.SSectNom == Secteur);

Why don't you want to create a query like the last block of code? That's the most efficient way to do it. — DavidG
– DavidG, Commented Jul 12, 2018 at 16:02
Performance issues, i dont want to retrieve all datas from sqlserver, then filtering, in my case i have more than 100 k lines in my tables. — d_raja_23
– d_raja_23, Commented Jul 12, 2018 at 16:08
Then you need to learn about IQueryable and how it works. Assuming _sourceDAL.Donneesource returns an IQueryable, then it will not be inefficient. — DavidG
– DavidG, Commented Jul 12, 2018 at 16:10
but how to create 3 where params with Iqueryable ? .Where(ic => ic.EstCopieDestination == false) .Where(s => PremierDateCom != DateTime.MinValue && s.SComPremierDate.Value.Date == PremierDateCom.Date) .Where(s => DerniereDateCom != DateTime.MinValue && s.SComDerniereDate.Value.Date == DerniereDateCom.Date) . if params with default value just want to ignore that clauses. — d_raja_23
– d_raja_23, Commented Jul 12, 2018 at 16:16

DavidG · Accepted Answer · 2018-07-12 16:19:19Z

Assuming _sourceDAL.Donneesource gives you an IQueryable<T> then you should be building up your query by adding Where clauses inside an if statement. This is because an IQueryable will not run a query against the database until you materialise it, i.e. with a foreach or a .ToList(). So something like this:

IQueryable<Donneesource> RawDatas = _sourceDAL.Donneesource .Where(ic => ic.EstCopieDestination == false) .Where(s => PremierDateCom != DateTime.MinValue && s.SComPremierDate.Value.Date == PremierDateCom.Date) .Where(s => DerniereDateCom != DateTime.MinValue && s.SComDerniereDate.Value.Date == DerniereDateCom.Date) .Where(s => Secteur != string.Empty && s.SSectNom == Secteur); if (PremierDateCom != DateTime.MinValue) { RawDatas = RawDatas.Where(x => x.SComPremierDate.Value.ToShortDateString() == PremierDateCom.ToShortDateString()); } if (DerniereDateCom != DateTime.MinValue) { RawDatas = RawDatas.Where(x => x.SComDerniereDate.Value.ToShortDateString() == DerniereDateCom.ToShortDateString()); } if (Secteur != null) { RawDatas = RawDatas.Where(x => x.SSectNom == Secteur); } //Now get the data from the database: return RawDatas.ToList();

Ali Kleit · Accepted Answer · 2020-11-26 10:22:42Z

To avoid using many if statements you can also try the following:

IQueryable<Donneesource> RawDatas = _sourceDAL.Donneesource .Where(ic => !ic.EstCopieDestination) .Where(s => PremierDateCom != DateTime.MinValue ? s.SComPremierDate.Value.Date == PremierDateCom.Date : true) .Where(s => DerniereDateCom != DateTime.MinValue ? s.SComDerniereDate.Value.Date == DerniereDateCom.Date : true) .Where(s => Secteur != string.Empty ? s.SSectNom == Secteur : true);

You can use a conditional statement, and if the condition is not satisfied, pass true instead.

Doesn't this still cause sequential conditions? i.e. the third .Where might filter elements which would've passed the fourth .Where check.

Collectives™ on Stack Overflow

Entity Framework Core LINQ query with multiple where clauses

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related