Compare a string column from a joined table to an array of optional strings in LINQ / entity framework

Question

I have a .net 4.6 webAPI where I am trying to pass in an optional parementer which will be an array of strings called locations.

I would like to determin if I passed in this parameter or not and then if I do have an array of locations I would like to determine if a column from a joined table/model is equal to any of the strings in the locations array.

I used an if else to switch between the two queries where I am comparing to the locations which seems adequate.

My issue is i do not know how to iterate through all elements in the locations array and compare it to the entire array rather than one element in the line that says dealer.Location.Contains(x)). I can compare it to a single element X but I cannot correctly implement this strategy to compare it all at once to an array of strings rather than a single string outlined in [This post(non accepted answer with the most votes is what I was trying to go for an failed).[this link]] I change my where line to locations.Any(dealers.LocationName.Contains)) The error i get when trying that is

{"Internal .NET Framework Data Provider error 1025."}

Update: i Thought i may need to throw a AsQueryable after the locations in my where clause however that doesnt seem to fix it

 public IHttpActionResult getAllCars([FromUri] string[] locations=null)
    {
        string y = "foo";
        string x = locations[0] ?? y;
        using (dbContext db = new dbContext())
        {
            if (locations == null)
            {
               var query = (from results in db.Cars
                             join dealers in db.Dealers on results.DealerID equals dealer.DealerNumber
                             select new
                             {
                                 ID = results.VinNumber,
                                 make = results.Make,
                                 model = results.Model,
                                 Dealer = dealers.DealerName
                             });
                return Ok(query.ToList());
            }
            else
            {
                var query = (from results in db.Cars
                             join dealers in db.Dealers on results.DealerID equals dealer.DealerNumber
                            where (dealer.LocationName.Contains(x))
                             select new
                             {
                                 ID = results.VinNumber,
                                 make = results.Make,
                                 model = results.Model,
                                 Dealer = dealers.DealerName
                             });
                return Ok(query.ToList());
            }

        }
    }

Are you trying to get the list of cars that are at those locations? Or a boolean indicating whether any results exist? — Derpy, Jun 28 '16 at 20:48
I am trying to get a list of cars at those locations not a boolean sorry for being unclear — ngnewb, Jun 28 '16 at 20:51

Ivan Stoev · Accepted Answer · 2016-06-28T21:00:08.543

if I do have an array of locations I would like to determine if a column from a joined table/model is equal to any of the strings in the locations array.

You can use locations Contains method for that.

Also you can simplify the query by putting the dealers into variable, optionally applying the filter and then use it inside the query like this:

using (dbContext db = new dbContext())
{
    var dealers = db.Dealers.AsQueryable();
    if (locations != null)
        dealers = dealers.Where(dealer => locations.Contains(dealer.LocationName));

    var query = (from car in db.Cars
                 join dealer in dealers on car.DealerID equals dealer.DealerNumber
                 select new
                 {
                     ID = car.VinNumber,
                     make = car.Make,
                     model = car.Model,
                     Dealer = dealer.DealerName
                  });

    return Ok(query.ToList());
}

P.S. If you actually need a partial match as in your original code, then you can use Any method instead:

    if (locations != null)
        dealers = dealers.Where(dealer => locations.Any(location => dealer.LocationName.Contains(location)));

Compare a string column from a joined table to an array of optional strings in LINQ / entity framework

1 Answers1