Convert SQL to LINQ Query

Question

I am having the following SQL query and I want to have it in LINQ, I tried many things but I can't get it working.

Below is my SQL query.

SELECT     ST.Description, ST.STId, COUNT(SI.SIId) AS Expr1
FROM         BP INNER JOIN
                      MbrBP ON BP.BPId = MbrBP.BPId INNER JOIN
                      SI ON BP.BPId = SI.BPId RIGHT OUTER JOIN
                      ST ON SI.STId = ST.STId
WHERE     (BP.CountryId = 1) AND (BP.RegionId = 1) AND (MbrBP.MemberId = 1)
      AND (SI.IsActive = 1)
GROUP BY ST.Description, ST.STId
UNION 
SELECT      ST.Description, ST.STId, COUNT(SI.STId) AS Expr1
FROM         SI RIGHT OUTER JOIN
                      ST ON SI.STId = ST.STId
GROUP BY ST.Description, ST.STId  

Answer 1

You can try Linqer. It is an SQL-> LINQ converter. 

Or you can also try the LINQPad.

Using these sites, you can convert SQL->Linq.

Looking for SQL Tutorial? Sign up for the SQL Certification course to gain more knowledge on SQL.

Answer 2

There exist two methods to turn SQL query into LINQ query that is written in C#, method syntax or query syntax. The following is the LINQ translation of your SQL query taking into account both strategies. This assumes that you have the requisite entity classes and a DbContext set up for the Entity Framework.

By Using method

var result = (from bp in context.BPs

              join mbr in context.MbrBPs on bp.BPId equals mbr.BPId

              join si in context.SIs on bp.BPId equals si.BPId into siGroup

              from si in siGroup.DefaultIfEmpty()

              join st in context.STs on si.STId equals st.STId

              where bp.CountryId == 1 && bp.RegionId == 1 && mbr.MemberId == 1 && si.IsActive == 1

              group si by new { st.Description, st.STId } into g

              select new 

              {

                  g.Key.Description,

                  g.Key.STId,

                  Expr1 = g.Count(s => s != null) // Count only non-null SI

              })

              .Union(

              from si in context.SIs

              join st in context.STs on si.STId equals st.STId into siGroup2

              from si in siGroup2.DefaultIfEmpty()

              group si by new { si.STId, si.ST.Description } into g

              select new 

              {

                  g.Key.Description,

                  g.Key.STId,

                  Expr1 = g.Count(s => s != null) // Count only non-null SI

              }

              ).ToList();

 

By using Query

var result = (from bp in context.BPs

              join mbr in context.MbrBPs on bp.BPId equals mbr.BPId

              join si in context.SIs on bp.BPId equals si.BPId into siGroup

              from si in siGroup.DefaultIfEmpty()

              join st in context.STs on si.STId equals st.STId

              where bp.CountryId == 1 && bp.RegionId == 1 && mbr.MemberId == 1 && si.IsActive == 1

              group si by new { st.Description, st.STId } into g

              select new 

              {

                  Description = g.Key.Description,

                  STId = g.Key.STId,

                  Expr1 = g.Count(s => s != null) // Count only non-null SI

              })

              .Union(

              from si in context.SIs

              join st in context.STs on si.STId equals st.STId into siGroup2

              from si in siGroup2.DefaultIfEmpty()

              group si by new { st.Description, st.STId } into g

              select new 

              {

                  Description = g.Key.Description,

                  STId = g.Key.STId,

                  Expr1 = g.Count(s => s != null) // Count only non-null SI

              }

              ).ToList();