linq join using lamda expressions


So now back to LINQ and this time we will be discussing about joins using LINQ. But the interesting point in this will be the use of lamda expressions and not the normal linq query. Of-course, we have the concept of navigation properties of the related entities in entity framework, but sometimes, our situation may demand the use of data from two entities where creating the relation between them is not required/possible for our concepts.

To start with, we will be having two tables with the following structure :

Entities

Add some dummy data into it and it will be ready to use.

Now we will be using the join between these two tables to fetch the data. Our first approach will be to use the SQL query to get the data, Our sql query will look something like the one below along with the results :

SQLQuery

Now, we will convert the above query into the LINQ query using lamda expressions & try to understand how the linq query is being written, by comparing with our original SQL query. Also, we will be having a look inside the linq query being executed, using the sql profiler. So let’s first have a look into the lamda expression based query and then we will discuss the steps :

LINQQuery

Here we divide the query into 5 different steps. These are :

Step 1 :  This is the main/actual table from which we are fetching the required data.

Step 2 :  Use the JOIN extension method and apply the join with the other tables. The first parameter for this extension method is the table with which we want to apply the join, of the main table.

Step 3 :  This is the second parameter of the extension method, which specifies the Primary column of the main table, which will be part of our join query.

Step 4 : This is the third parameter of the extension method, which specifies the Primary column of the second table, with which we want to apply the join, of the main table.

Step 5 : This specifies the list of columns that we want to retrieve from both the tables. This is basically our SELECT query.

Step 6 : This is just to cause the final execution of our query.

So, its the step 1-5, which is the linq equivalent of our actual SQL query that we created.

Now let’s see what’s the actual query generated behind the scenes, by using the SQL profiler  :

Profiler

So here we can see that this query is nothing but the same that we had written earlier in the article. Happy coding…!!!

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in C#, Linq and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s