score:3
Accepted answer
from l in db.Leads
from a in l.LeadAddresses.Select(la => la.Address).OrderByDescending(a => a.ID).Take(1).DefaultIfEmpty()
select new { l, a }
This might look tricky, but you understand it part by part:
- Using
OrderByDescending
in combination withTake(1)
we take the address with the maximum ID - Using DefaultIfEmpty we create a left-join.
Be aware that this pattern forces a loop-join due to limitation of SQL Server. For small result sets this is usually not a problem.
Source: stackoverflow.com
Related Articles
- Joining and grouping multiple tables with LINQ when using a linking table
- How to reuse a linq expression for 'Where' when using multiple source tables
- Get data with Sum from a table after multiple joining using LINQ
- How to select needed records in joining table using LINQ with tables which have many to many relationship?
- Joining two tables with LINQ while also returning null records from the second table
- Problem with simple join by multiple columns in two tables using linq
- Populate a C# class from a dataset with multiple linked tables using linq lambda
- Joining two tables and returning multiple records as one row using LINQ
- Linq sub query when using a repository pattern with EF code first
- C# Linq - Issue with joining tables with multiple fields - Error CS1941
- Issue with joining tables in LINQ when needing a left outer join type
- Left Join 2 tables with main table using LINQ
- C#: LINQ query grouping by multiple columns in data table using Field method
- Joining multiple tables to return a list using LINQ
- How to make types match in LINQ to Entities when using .GroupJoin extension with multiple conditions?
- Joining or Including multiple tables with Entity Framework Core LINQ to Entities - One to Many and then Many to One
- Using AutoMapper when having a LINQ statement with multiple .Include
- AP.NET webforms using LINQ to SQL datasource and databind with foreign keys across multiple tables
- Unable to return list in linq query when using different tables with inner joins
- How can I make a JOIN query with LINQ between two tables (entities) with many-to-many relationship, while there is no joining table in EF?
- Grouping in Linq queries with multiple tables
- Joining 3 tables and using a left outer join with linq in EF Core 3.1.1
- SQL to LINQ - multiple tables left outer join with where clause referring right table
- Search multiple tables with no relation by special value with one query using linq to entity
- grouping data from multiple tables using linq
- Can't Select Records from a database table with grouping using Linq
- How can I use this Linq Query using it with a multiple parameter by Join tables
- Joining multiple one-to-many tables with Linq to SQL
- I want to bring data from multiple tables with entityframework using linq method in dotnetcore web api controller
- How to Update multiple tables with foreign keys using LINQ expression ASP.NET MVC Entity Framework
- C# Query XML with Linq to find match
- Expression for ICollection<T>.Any(Func<T, bool>)
- Entity Framework ASP.Net MVC return only the desired records directly from the Entity Model
- LINQ: sort child object param
- C# Count removed nodes
- LINQ: what is the type of a result?
- Does LINQ skip & take have decent performance?
- SQL -> LINQ (C#)
- Algorithm for tagging list of substrings from a list of strings in C#
- NHibernate and LINQ - InvalidOperationException: "Could not find entity named ..."
- "LINQ to Entities does not recognize the method 'Boolean ***, and this method cannot be translated into a store expression."
- How does deferred LINQ query execution actually work?
- LINQ & XNode.DeepEquals running comparisons twice?
- How can I efficiently do master/detail grouping in Linq from a flat enumerable where there is no hard link from master to detail?
- How does one increment/update integer value of an XML Attribute in C# (XAttribute)?
- LINQ to SQL, sorting by a related table
- Get min DateTime from Linq
- How I can optimize my LINQ query?
- Exclude control container from list
- Finding the aggregate sum an operation in linq