C# join tables using IQueryable in LINQ

Yesterday I was struggling with how to Join tables using IQueryable in C# or Asp.Net and return it in List. So, here I’m with the solution. Let’s start..

With the introduction of LINQ the difference between writing code for  accessing a list of data in an external data source like SQL Server is vanishing. With the introduction of .NET Framework 4.0 this has changed.In this post i would like to filter my SQL data employing 2 tables.

In my project I’ve 2 tables. 1: Containing all the basic user data like Full Name, DOB etc and 2nd containing user’s student id, card number and so on.. I’ve needed a Data like this: Full Name [Roll Number]

So, here’s the code

var query = (from a in DbInstance.User_Account
join s in DbInstance.User_Student on a.Id equals s.Id
where s.Status == 1
select new { a, s } into t1
select new
{
Id = t1.a.Id.ToString(),
Name = string.Concat(t1.a.FullName.ToString(), ” [“, t1.s.RollNumber.ToString(), “]”)
}).ToList();