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

5

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.

edited Nov 26, 2020 at 10:22

Ali Kleit

3,7793 gold badges28 silver badges44 bronze badges

answered Nov 26, 2020 at 8:55

Argiris Pagidas

611 silver badge3 bronze badges

1 Comment

TimTam Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related