How to get data by mutiple values on one column with different values on other columns from database in Entity Framework?

Question

I would like to search the data from database from two tables with multiple optional values.

[HttpPost]
public ActionResult GetAll(Details objDetail)
{

First I am creating AsQueryable object for querying.

var searchQuery = (from acc in AppDB.tblAccount
                   join user in AppDB.tblUser on acc.uID equals user.uID
                   select new { acc, user }).AsQueryable();

Then I'm applying conditions to this AsQueryable object one by one.

if (objDetail.Type != null)
    searchQuery = searchQuery.Where(x => x.acc.Type == objDetail.Type);

if (objDetail.Category!= null)
    searchQuery = searchQuery.Where(x => x.acc.Category== objDetail.Category);

if (objDetail.City.Length != 0)
    for (int i = 0; i <= objDetail.City.Length - 1; i++)
    {
        string singleCity = objDetail.City[i];
        searchQuery = searchQuery.Where(x => x.user.City.Contains(singleCity));
    }

if (objDetail.Place!= null)
    searchQuery = searchQuery.Where(x => x.user.Place== objDetail.Place);

if (objDetail.Price != null)
    searchQuery = searchQuery.Where(x => x.acc.Price == objDetail.Price);

searchQuery = searchQuery.Where(x => x.user.IsActive == true && 
                                     x.user.IsDelete == false &&
                                     x.acc.IsActive == true && 
                                     x.acc.IsDelete == false);

Then finally I'm trying to get the data from searchQuery object into some other object by selecting columns like below

var searchedAccList = (from result in searchQuery
                       select new
                              {
                                 Id = result.acc.aID,
                                 UserId = result.user.uID,
                                 Name = result.user.Name,
                                 AccountType = result.acc.Type,
                                 City = result.user.City,
                              }).ToList();
    return Json(searchedAccList, JsonRequestBehavior.AllowGet);
}

I am getting zero records where as I have records, and if I send single city value then getting records.

So how to get data by multiple city values?

user_1875256 · Answer

As you are looping you will end up with rows for the last city in the loop that comes. You need to use Any or contains in single where for all cities like:

searchQuery = searchQuery.Where(x => objDetail.City.Contains(x.user.City));

Now it will bring all rows in search matching the cities in the collection objDetail.City

How to get data by mutiple values on one column with different values on other columns from database in Entity Framework?

1 Answers