CODEWITHSUNDEEP
c#entity-frameworkjoinlinq-to-entities
aaa
aaa

Reputation: 1124

Join table with object list

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.

Upvotes: 3

Views: 3624

Answers (4)

skelton
skelton

Reputation: 1

I landed up passing in an xml list as a parameter to the sql query and joined to that:

var xml = new XElement("Cars", yourlist.Select(i => new XElement("Car", new XElement("Id", i.Id), new XElement("Model", i.Model))));
var results = Cars
        .FromSql("SELECT cars.*"
                + "FROM @xml.nodes('/Cars/Car') Nodes(Node)"
                + "JOIN Cars cars on cars.Id = Nodes.Node.value('Id[1]', 'int') and cars.Model = Nodes.Node.value('Model[1]', 'varchar(100)')",
            new SqlParameter("@xml", new SqlXml(xml.CreateReader())));

For entity-framework-core users I created a nuget package extension:

EntityFrameworkCore.SqlServer.Extensions.Contains

Upvotes: 0

Alejandro del R&#237;o
Alejandro del R&#237;o

Reputation: 4046

Old school solution..

//in case you have a 
List<CarSearch> search_list; //already filled

List<Cars> cars_found = new List<Cars>();
foreach(CarSearch carSearch in search_list)
{
    List<Cars> carsFromQuery = context.Cars.Where(x => x.Id == carSearch.Id && x.Model == carSearch.Model).ToList();
    cars_found.AddRange(carsFromQuery);
}

Abd don't worry about the for loops.

Upvotes: 0

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

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")

Upvotes: 1

Sorax
Sorax

Reputation: 2203

'PredicateBuilder' might be helpful.

var predicate = PredicateBuilder.False<Car>();
carSearchObjects
.ForEach(a => predicate = predicate.Or(p => p.Id == a.Id && p.Model == a.Model));

var carsFromQuery = context.Cars.AsExpandable().Where(predicate);

Note the text in the link regarding EF:

If you're using Entity Framework, you'll need the complete LINQKit - for the AsExpandable functionality. You can either reference LINQKit.dll or copy LINQKit's source code into your application.

Upvotes: 1

Related Questions