CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sqllinq-to-entities
Anish
Anish

Reputation: 912

Dynamically Building LINQ-To-Entities Where Clause

How can I build the where clause dynamically, Some time the OwnerID will be zero only itemID and LocationIDwill be provided as the search criteria, in that case the LINQ should be

(from s in repository.ItemOwners.Include("OwnerDetails")
 where s.ItemId == searchCriteria.ItemID && 
       s.OwnerDetails.LocationId == searchCriteria.LocationID
 select new { s.OwnerDetails.OwnerName, s.OwnerDetails.MobNumber }).ToList();

Some time the OwnerID and ItemId will be zero then only the LocationID will be provided as the search criteria, in that case the LINQ should be

(from s in repository.ItemOwners.Include("OwnerDetails")
 where s.OwnerDetails.LocationId == searchCriteria.LocationID
 select new { s.OwnerDetails.OwnerName, s.OwnerDetails.MobNumber }).ToList();

Some time the whole OwnerID, ItemID and LocationID will be provided as the search criteria, then the LINQ will be like this

(from s in repository.ItemOwners.Include("OwnerDetails")
 where s.OwnerId == searchCriteria.OwnerID && 
       s.ItemId == searchCriteria.ItemID && 
       s.OwnerDetails.LocationId == searchCriteria.LocationID
 select new { s.OwnerDetails.OwnerName, s.OwnerDetails.MobNumber }).ToList();

Here only the where clause is changing, Please help me to solve. How I can I build the where clause dynamically (Please note, here I am having a navigation property which is OwnerDetails.LocationId).

Upvotes: 1

Views: 209

Answers (2)

ChrisV
ChrisV

Reputation: 1309

Simplest is just check the zero condition in the Where clause:

(from s in repository.ItemOwners.Include("OwnerDetails")
where (searchCriteria.OwnerID == 0 || s.OwnerId == searchCriteria.OwnerID) && 
(searchCriteria.ItemID == 0 || s.ItemId == searchCriteria.ItemID) && 
s.OwnerDetails.LocationId == searchCriteria.LocationID
select new { s.OwnerDetails.OwnerName, s.OwnerDetails.MobNumber }).ToList();

Upvotes: 0

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

You can easily do it by using method-based query. You can add the conditions one at a time and call Select and ToList at the end:

// Where(x => true) might not be necessary, you can try skipping it.
var query = repository.ItemOwners.Include("OwnerDetails").Where(x => true);

if (searchCriteria.OwnerID != null)
    query = query.Where(s => s.OwnerID == searchCriteria.OwnerID);
if (searchCriteria.ItemID != null)
    query = query.Where(s => s.ItemID == searchCriteria.ItemID);
if (searchCriteria.OwnerID != null)
    query = query.Where(s => s..OwnerDetails.LocationId == searchCriteria.LocationID);

var results = query.Select(s => new { s.OwnerDetails.OwnerName, s.OwnerDetails.MobNumber }).ToList();

Upvotes: 3

Related Questions