About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Monday, February 04, 2013

A couple of Linq to Sql Query Examples


Just a quick reference of a couple basic query conversions for Linq to SQL




Code Snippet
  1. //inner join
  2.             //SELECT [t0].[CountyName]
  3.             //FROM [dbo].[County] AS [t0]
  4.             //INNER JOIN [dbo].[State] AS [t1] ON [t0].[StateID] = [t1].[StateID]
  5.             //WHERE LOWER([t1].[StateName]) = @p0
  6.             var counties =  from c in db.Counties
  7.                             join st in db.States on c.StateID equals st.StateID
  8.                             where st.StateName.ToLower().Equals("florida")
  9.                             select c.CountyName;
  10.  
  11.  
  12.             //left join -- use DefaultIfEmpty and new object
  13.             //SELECT [t0].[CountyName],
  14.             //(CASE WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),@p0)
  15.             //      ELSE CONVERT(NVarChar(50),[t2].[StateName])
  16.             //      END) AS [StateName]
  17.             //FROM [dbo].[County] AS [t0]
  18.             //LEFT OUTER JOIN
  19.             //(
  20.             //      SELECT 1 AS [test], [t1].[StateID], [t1].[StateName]
  21.             //      FROM [dbo].[State] AS [t1]
  22.             //) AS [t2] ON [t0].[StateID] = [t2].[StateID]
  23.             var counties2 = from c in db.Counties
  24.                             join st in db.States on c.StateID equals st.StateID
  25.                             into stateCounties
  26.                             from co in stateCounties.DefaultIfEmpty()
  27.                             select new { CountyName = c.CountyName, StateName = (co == null)? "": co.StateName };
  28.  
  29.  
  30.             //Same table query
  31.             //SELECT [t0].[CountyName]
  32.             //FROM [dbo].[County] AS [t0],
  33.             //     [dbo].[County] AS [t1]
  34.             //WHERE ([t0].[CountyName] = [t1].[CountyName]) AND ([t0].[CountyID] <> [t1].[CountyID])
  35.             var counties3 = from c1 in db.Counties
  36.                             from c2 in db.Counties
  37.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
  38.                             select new
  39.                             {
  40.                                 CountyName = c1.CountyName,
  41.                             };
  42.  
  43.  
  44.  
  45.  
  46.             var counties4 = (from c1 in db.Counties
  47.                             from c2 in db.Counties
  48.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
  49.                             group c1 by c1.CountyName into dups
  50.                             select new
  51.                             {
  52.                                 CountyName = dups.Key,
  53.                                 Count = dups.Count()
  54.                             }
  55.                             ).OrderBy(n => n.CountyName);

Some resources I found useful:
http://codesamplez.com/database/linq-to-sql-join-tutorials
http://weblogs.asp.net/rajbk/archive/2010/03/12/joins-in-linq-to-sql.aspx

http://msdn.microsoft.com/en-us/library/bb386913.aspx
   More specifically to joins:
   http://msdn.microsoft.com/en-us/library/bb399397.aspx

No comments: