Join table with object list

linq join two lists
linq to objects join
c# join list of objects
c# inner join two lists
linq join two lists multiple conditions
c# join two lists on id
c# linq join two tables
c# linq join lambda

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.

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

Perform inner joins (LINQ in C#), The query uses the join clause in C# to match Person objects with Pet The following example uses a list of Employee objects and a list of  LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records. RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records. Note: All INNER and OUTER keywords are optional. Details about the different JOINs are available in subsequent tutorial pages.

'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.

SQL joining tables on collection list, SQL joining tables on collection list ⏩ Post By ✅ Giray Ozel all I could find was an example, which used a list of string instead of list of objects. And that worked, but still had some drawbacks (Npgsql only; need to write to temp tables; can’t write real queries). Now I decieded to do something different which will help me to create RDBMS agnostic solution and which will allow joining to in-memory list and with writing real EF queries. So can I join a table to a list using linq? TL;DR

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.

Joining to in memory List, Even then, joining is rather inefficient because EF has to convert the local list into a temporary SQL table But now you haven't got EmployeeData objects yet. Uploading can be done differently: using some temp tables OR using VALUES. Mark 6/13/2012 10:15:00 AM. Join-Object’s first two parameters are the left and right lists of objects. The “Where” parameter is the join condition, and it uses $args [0] to represent an item from the left list and $args [1] for an item in the right list.

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

SQL Tutorial: Joining, Data on the same objects are often stored in different tables. You can list more than one table in the from block, for example "from photoObj, specObj." But be  Today, in this article, we will see the step-by-step process of joining multiple tables using LINQ Join and displaying records in a View. So, let's start. Step 1.

SQL Join Query With LINQ, As we know the JOIN clause is very useful when merging more than two table or object data into a single unit. It combines different source  Pandas .join(): Combining Data on a Column or Index. While merge() is a module function, .join() is an object function that lives on your DataFrame. This enables you to specify only one DataFrame, which will join the DataFrame you call .join() on.

Bookshelf v8.0: Creating a Joined Business Component Field, In the Object Explorer, expand the Business Component tree, and then click Join. In the Joins list, add a new record, using values from the following table. On the Attributes page, click New to add an attribute that corresponds to the columns in the static list table. In the New View Object Attribute dialog, enter a name and select the attribute type. Click OK to return to the wizard, and click Next. On the Attribute Settings page, do nothing and click Next.

Joining Data Sources, Automatically Add Join Objects; Join Data Sources; Create a Join Condition With the A left outer join includes all of the rows from the table listed in the Left Source select one column from each list, and click the equal sign to create the join. For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

Comments
  • Will the performance of the query be significantly reduced with all the conversions as opposed with simple joins?
  • Well, you just can't use join with objects and db entities. The performance reduction is just coming from the fact that (if I'm not wrong) the generated sql won't use indexes with concatenated datas.
  • Don't worry about the loops, really ? In this case (db query), every loop will be a db call. So it may be a problem... (depending on the size of the search_list, of course). By the way you could just use cars_found.AddRange(carsFromQuery), but that's a detail.
  • I asume the car search list its not going to be too large. In any case the timing its not mentioned in the question. And in any any case I don't think it's going to be a problem like the world is going to end. Thanks for the detail I've edited my answer.