Writing Complex Queries Using LINQ

I am writing this article based on my recent experience regarding how to write some complex queries using LINQ.

I’m not going to explain why we should use LINQ over lambda or neither Query Syntax vs. Method syntax in LINQ.

Let’s dig in..

LINQ Join column from 2 table:

Just for the demo, I have created Building & Resource classes. Resource having a relationship with Building via BuildingId column.

Now I have to write a query that returns ‘Distinct’ and ‘Active’ resources list i.e: Resource Id & Resource Name- Building Name. Ex: Physics Lab-Building 01. Here ‘Lab’ is the Resource Name and ‘Building 01’ is the Building name.

Id	Name
100	Physics Lab-Building 01
101	Chemistry Lab-Building 02

var resourcequery = (from resource in DbInstance.Resource
join building in DbInstance.Building on resource.BuildingId equals building.Id
where resource.IsActive == true
select new { resource, building } into t1
select new
{
Id = t1.resource.Id.ToString(),
Name = t1.resource.Name + "-" + t1.building.Name
}).Distinct().ToList();

Here is the final output: