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 Query
- 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
- Joining two tables and returning multiple records as one row using LINQ
- Joining or Including multiple tables with Entity Framework Core LINQ to Entities - One to Many and then Many to One
- AP.NET webforms using LINQ to SQL datasource and databind with foreign keys across multiple 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 3 tables and using a left outer join with linq in EF Core 3.1.1
- convert SQL query with multiple join on multiple tables using group by on multiple columns and with aggregate function into LINQ
- Returning a Query Result to an ListView when joining tables Using Visual Basic and Linq
- Join Parent, Child and GrandChild tables with latest record from grandchild table using LinQ in C#
- Query a SQL Server table with multiple variables using LINQ and C# and return the results
- Access all of the data after joining two tables and group them using linq
- LinQ query with multiple tables and extracting data
- Joining two tables with LINQ while also returning null records from the second table
- Grouping and multiple orderings using Linq to Entities
- Duplicate rows when using orderby, Skip() and Take() with LINQ
- Using LINQ left join with multiple conditions and subquery
- LINQ to SQL join 3 tables and select multiple columns and also using Sum
- 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
- How to format linq to replace AND with OR when combining multiple Where's
- Pull data from multiple tables in one SQL query using LINQ and Entity Framework (Core)
- Entity Framework: How to perform left join with EF and LINQ among multiple tables
- Linq sub query when using a repository pattern with EF code first
- Insert into multiple tables (one to one relationships) with Linq queries and SQLite
- Updating multiple tables with LINQ and ASP.NET MVC
- LINQ to SQL bug (or very strange feature) when using IQueryable, foreach, and multiple Where
- C# Linq - Issue with joining tables with multiple fields - Error CS1941
- Multiple Tables Group and substract sum of columns using linq sql
More Query from same tag
- The result of a query cannot be enumerated more than once when using stored procedure
- How do I iterate through a dictionary's keys and execute a method for each key in the dictionary using Linq?
- How do I remove elements from an object of type Table?
- Convert List of array into the list of KeyValuePairs using LINQ
- Performance of Find() vs. FirstOrDefault()
- LINQ - join with OR condition
- General method to select first row from every group in DataTable, give a List of grouping column
- Performing part of a IQueryable query and deferring the rest to Linq for Objects
- Get full rows based on single column distinct
- Sort a list alphabetically excluding a letter
- How to group data and store them in a new list by group
- Do you choose Linq over Forloops?
- Get 1 record from 2 tables ASP.NET
- Get top 3 most occuring numbers in a List
- C# Extract list of fields from list of class
- Inspection of Insert Statement When Using LINQ's SubmitChanges
- SQL to LINQ - Multiple Group By Statements and a Join
- Reduce linq query for filtering
- EF Core - multiple join conditions causing CS1941
- How to return IEnumerable Linq value with conditional statement
- Serialization in WCF/LINQ to SQL
- Nested Selects in LINQ
- C# printing LINQ-table to console without specifying the properties
- Subsonic Query Construction for Contains(Guid)
- How to use a precompiled Linq query
- C# - LINQ query returns null to table and throws exception
- Can I do a subquery in in SELECT of LINQ to Entities to fill List in model?
- Outer join in LINQ Problem
- How to find duplicates in a List<T> quickly, and update the original collection
- Query and map complex objects