Entity Framework Core LINQ query with multiple where clauses
Solution 1
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();
Solution 2
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.
d_raja_23
Updated on June 09, 2022Comments
-
d_raja_23 almost 2 years
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);