LINQ query with value from dropdown to compare with data

Question

My query is below. Can someone help me how to add dbquery inside my Linq statement? There is a comment "Add Where here". I'm struggling since yesterday. Idea is to form a LINQ statement and get the list at once. Thank you.

String dbwhere = "";
if (ddlName.SelectedItem.Value != "")
{
    dbwhere = " && (User.Name == '" + ddlName.SelectedItem.Value.TrimEnd() + "')";
}
if (ddlHeightFrom.SelectedItem.Value != "")
{
    dbwhere = dbwhere + " && (Physical.Height >= '" + ddlHeightFrom.SelectedItem.Value.TrimEnd() + "')";
}
if (ddlHeightTo.SelectedItem.Value != "")
{
    dbwhere = dbwhere + " && (Physical.Height <= '" + ddlHeightTo.SelectedItem.Value.TrimEnd() + ")";
}

var usersquery = (
  from physical in dbContext.Physicals
  join user in dbContext.User on physical.UserID equals user.UserID
  join photos in dbContext.Photo on User.UserID equals photos.UserID
  where photos.PhotoNum == 1 && photos.Status == true
  // =======  Add dbwhere here ============
  select new
  {
     photos.PhotoURL,
     photos.PhotoDescription,
     user.State,
     user.Country,
     physical.EyesColor,
     physical.HairColorInfo,
     physical.HairTypeInfo,
     physical.BodyHeight,
     physical.BodyWeight,
  }).ToList();

score 1 · Accepted Answer · answered Oct 14 '17 at 19:39

1

You can rewrite your query to avoid mixing linq with SQL (and make it safe from SQL injections)

var usersquery = (
    from physical in dbContext.Physicals
    join user in dbContext.User on physical.UserID equals user.UserID
    join photos in dbContext.Photo on User.UserID equals photos.UserID
    where photos.PhotoNum == 1 && photos.Status == true
    select new
    {
        physical,
        user,
        photos,
    }; // do not put ToList here!

Now you can add your special checks:

if (ddlName.SelectedItem.Value != "")
{
  var userName = ddlName.SelectedItem.Value.TrimEnd();
  usersquery = usersquery.Where(x => x.user.Name == userName);
}

if (ddlHeightFrom.SelectedItem.Value != "")
{
  var height = int.Parse(ddlHeightFrom.SelectedItem.Value.TrimEnd());
  usersquery = usersquery.Where(x => x.physical.Height >= height);
}

// and so on

Now you can materialize your data with ToList

var result = usersquery.Select(x => new 
  {
    x.photos.PhotoURL,
    x.photos.PhotoDescription,
    x.user.State,
    x.user.Country,
    x.physical.EyesColor,
    x.physical.HairColorInfo,
    x.physical.HairTypeInfo,
    x.physical.BodyHeight,
    x.physical.BodyWeight
  }).ToList();

NOTE: I've write it in notepad, so it may have errors. However I hope idea is clear

answered Oct 14 '17 at 19:39

Aleks Andreev

7,016
8
29
37

The difficult part is there will be huge data and hope this is fine with not slowing down the loading time I'll try. Thank you. – dotNETEngineer Oct 14 '17 at 19:46
@dotNETEngineer when your create `usersquery` first time no actual data fetching is happen. Data will be queried only after applying all filters (if present) whith `ToList()` method. So my approach will query db only once. – Aleks Andreev Oct 14 '17 at 19:57
Awesome, getting some casting issues here, but I'm researching ,... var race = ddlRace.SelectedItem.Value.TrimEnd(); photosquery = photosquery.Where(x => x.Race == race); iEnnumerable to GeneralList – dotNETEngineer Oct 14 '17 at 19:59
When I add ToList() at the end, it works. var race = ddlRace.SelectedItem.Value.TrimEnd(); photosquery = photosquery.Where(x => x.Race == race).ToList(); – dotNETEngineer Oct 14 '17 at 20:03
As you mentioned, I'm not suppose to put ToList() before result selection (end), I'm working on it. – dotNETEngineer Oct 14 '17 at 20:05
Going forward I'd also recommend to replace explicit `join` with `Navigation Properties`. See https://msdn.microsoft.com/en-us/library/jj591620(v=vs.113).aspx – abatishchev Oct 14 '17 at 20:39
Good idea, thanks a lot of learning for me. Thanks a lot for throwing me an idea for splitting LINQ Query. From now, I will move on. – dotNETEngineer Oct 15 '17 at 02:22
@AleksAndreev If you have a minute, would you like to answer my other question? Here is the link: https://stackoverflow.com/questions/46760678/calculateage-or-year-from-linq-query-datetime-field-using-c-sharp-for-comparis – dotNETEngineer Oct 15 '17 at 22:37
Hi , would you mind to check the below link and provide the answer? Thanks. – dotNETEngineer Jan 29 '20 at 16:54

LINQ query with value from dropdown to compare with data

1 Answers1