| by Achyut Kendre | No comments

Understand Linq Joins using DbFirst Example

ASP NET MVC 5

Join Code Example: – https://drive.google.com/file/d/1hNIyl4ggcolcPNCyE6meEH-uhcPixWLF/view?usp=sharing

Similar SQL Joins we can also implement the joins in LINQ. Linq provides you a join keyword which will help you to implement the joins in LINQ. Joins are required if you need the data from multiple tables together.

Inner Join or Simple Join In LINQ

This is similar to SQL inner join which will fetch record or instance if it exits in both the tables if not then it will skip it.

Syntax for the LINQ Join as follows –

var variablename = from variablename1 in datasource1  join variablename2 in datasource2 on variablename1.fieldname equals variablename2.fieldname 
select new {
objectname.fieldname,
objectname.fieldname ...
}

Assume the following tables and data

var v = from t1 in entity.ATbls join t2 in entity.BTbls on
t1.AID equals t2.AID select new {
t1.AID,
t1.AName,
t2.BID,
t2.BName,
t2.AID
}

Outer Joins in LINQ

Their is no built in keyword for outer joins in LINQ but we can get the effect of outer join in linq by making some arrangement.

Left Outer Join

In left outer join it will select data from left table(collection) and matching data from the right table (collection). In Linq their is no special arrangement for left outer join but we can achieve this result by making small arrangments as follows –

  1. create simple join
  2. load the join into some variable using into keyword
  3. select data from new variable where the join data is loaded. 
var left = from t in entity.ATbls
                    join t1 in entity.BTbls
                    on t.AID equals t1.AID
                    into g
                    from t2 in g.DefaultIfEmpty()
                    select new ABVM
                    {
                        AID = t.AID,
                        AName = t.AName,
                        BID = t2.BID,
                        BName = t2.BName
                    };

Right Outer Join

Similar to left outer join we can do the right outer join but their is no keyword for the same but if we reverse the left outer join we will get the effect of right outer join we can do the query as follows –

var right = from t in entity.BTbls
                    join t1 in entity.ATbls
                    on t.AID equals t1.AID
                    into g
                    from t2 in g.DefaultIfEmpty()
                    select new ABVM
                    {
                        AID = t2.AID,
                        AName = t2.AName,
                        BID = t.BID,
                        BName = t.BName
                    };

Full Outer Join

We can also implement full outer join by union keyword. We can generate the full outer join result by doing union of left outer join and right outer join. We can do it as follows –

var left = from t in entity.ATbls
                    join t1 in entity.BTbls
                    on t.AID equals t1.AID
                    into g
                    from t2 in g.DefaultIfEmpty()
                    select new ABVM
                    {
                        AID = t.AID,
                        AName = t.AName,
                        BID = t2.BID,
                        BName = t2.BName
                    };

            var right = from t in entity.BTbls
                    join t1 in entity.ATbls
                    on t.AID equals t1.AID
                    into g
                    from t2 in g.DefaultIfEmpty()
                    select new ABVM
                    {
                        AID = t2.AID,
                        AName = t2.AName,
                        BID = t.BID,
                        BName = t.BName
                    };
            var v= left.Union(right);

« Prev