Join table with object list

Question

I have a table, lets say tblCar with all the related columns like Id, Make, Model, Color etc.

I have a search model for car containing two params Id and Model.

public class CarSearch
{
   public int Id { get; set; }
   public string Model { get; set; }
}

var carSearchObjets = new List<CarSearch>();

With list of primitive data (like Id list), to get cars with those Ids I could have done:

var idList = new List<int> { 1, 2, 3 };
var carsFromQuery = context.Cars.Where(x => idList.Contains(x.Id);

But if I have to fetch all the cars with Id and model from the list, how do I do it? Simple join cannot be done between in memory objects and tables.

I need something like,

from m in context.Cars
join n in carSearchObjets 
     on new { Id = n.Id, Model = n.Model } equals new { Id = m.Id, Model = m.Model }
select m;

This obviously won't work.

Please ignore any typos.And if you need more info or the question is not clear, let me know.

Answer 1

One (ugly-but-working) way to manage that is to use concatenation with a "never used" concat char.

I mean a char that should never appear in the datas. This is always dangerous, as... never is never sure, but you've got the idea.

For example, we'll say that our "never used" concat char will be ~

This is not good for perf, but at least working :

var carSearchObjectsConcatenated = carSearchObjets.Select(m => new { m.Id + "~" + m.Model});

then you can use Contains again (concatenating on the db too) : you'll need to use SqlFunctions.StringConvert if you wanna concatenate string and numbers on the db side.

var result = context.Cars.Where(m => 
                carSearchObjectsConcatenated.Contains(SqlFunctions.StringConvert((double)m.Id) + "~" + m.Model);

EDIT

Another solution would be to use PredicateBuilder, as mentionned by Sorax, or to build your own Filter method if you don't want a third party lib (but PredicateBuilder is really fine).

Something like that in a static class :

public static IQueryable<Car> FilterCars(this IQueryable<Car> cars, IEnumerable<SearchCar> searchCars)
        {
            var parameter = Expression.Parameter(typeof (Car), "m");

            var idExpression = Expression.Property(parameter, "Id");
            var modelExpression = Expression.Property(parameter, "Model");

            Expression body = null;
            foreach (var search in searchCars)
            {
                var idConstant = Expression.Constant(search.Id);
                var modelConstant = Expression.Constant(search.Model);

                Expression innerExpression = Expression.AndAlso(Expression.Equal(idExpression, idConstant), Expression.Equal(modelExpression, modelConstant));
                body = body == null
                    ? innerExpression
                    : Expression.OrElse(body, innerExpression);
            }
            var lambda = Expression.Lambda<Func<Car, bool>>(body, new[] {parameter});
            return cars.Where(lambda);
        }

usage

var result = context.Cars.FilterCars(carSearchObjets);

this will generate an sql looking like

select ...
from Car
where 
 (Id = 1 And Model = "ax") or
 (Id = 2 And Model = "az") or
 (Id = 3 And Model = "ft")