-3

i am trying to write search query in linq based upon attributes of an object where object attributes can be null. i want to filter result if my column matches the value of some attribute. i.e object.country == "pakistan" then all the records of country pakistan should be displayed and if object.country is NULL then all records should be selected.

var query = from x in db.user_info
        join y in db.user_detail_info on x.Id equals y.Id
        where (key != null && key >= x.country) || (key == null)
        select x;
Usman Sahib
  • 1
  • You should tell us where your problem is. It's hard to help you. – Alexander Schmidt May 29 '16 at 00:42
  • suppose i am receiving a string as a parameter in a function which(string) contains the name of country which either contain a non null value or can be null now if it contains a non null value then i want to select those records from the table where country is same as parameter string and if parameter string is null then i want to select all the records. how can i do that ? about query is not returning desired result. – Usman Sahib May 29 '16 at 00:59
  • See my comment on @Callback Kids answer. – Alexander Schmidt May 30 '16 at 05:41

2 Answers2

0

What you are trying to do is essentially a left join, linq does not support left joins unfortunately. But there is a way around this.

Your linq becomes this...

var q =
    from x in db.user_info
    join y in db.user_detail_info on x.Id equals y.Id 
    where (key == null) || (x.country == key)
    select x;
Community
  • 1
  • 1
Callback Kid
  • 708
  • 5
  • 22
  • Just wanted to add that you can use `IQueryable.DefaultIfEmpty()` for left joins too. So in the sample this would be `db.user_detail_info.DefaultIfEmpty()`. – Alexander Schmidt May 30 '16 at 05:40
0

I believe you want something like this? PS - Unable to test it right now.

string country = null; // set this var to a country name
var peopleList = model.People
    .Join(model.PeopleDetails, 
        p => p.PeopleID, 
        pd => pd.PeopleID,  
        (p, pd) => new { p, pd })
    .Where(resultSet =>  
        string.IsNullOrWhiteSpace(country) 
            ? true 
            : resultSet.pd.CountryName.Equals(country))
    .Select(resultSet => resultSet.p)
    .ToList();
njasm
  • 431
  • 2
  • 6