A couple of Linq to Sql Query Examples

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

Code Snippet

21. //inner join
22.             //SELECT [t0].[CountyName]
23.             //FROM [dbo].[County] AS [t0]
24.             //INNER JOIN [dbo].[State] AS [t1] ON [t0].[StateID] = [t1].[StateID]
25.             //WHERE LOWER([t1].[StateName]) = @p0
26.             var counties =  from c in db.Counties
27.                             join st in db.States on c.StateID equals st.StateID
28.                             where st.StateName.ToLower().Equals("florida")
29.                             select c.CountyName;
30.  
31.  
32.             //left join -- use DefaultIfEmpty and new object
33.             //SELECT [t0].[CountyName],
34.             //(CASE WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),@p0)
35.             //      ELSE CONVERT(NVarChar(50),[t2].[StateName])
36.             //      END) AS [StateName]
37.             //FROM [dbo].[County] AS [t0]
38.             //LEFT OUTER JOIN
39.             //(
40.             //      SELECT 1 AS [test], [t1].[StateID], [t1].[StateName]
41.             //      FROM [dbo].[State] AS [t1]
42.             //) AS [t2] ON [t0].[StateID] = [t2].[StateID]
43.             var counties2 = from c in db.Counties
44.                             join st in db.States on c.StateID equals st.StateID
45.                             into stateCounties
46.                             from co in stateCounties.DefaultIfEmpty()
47.                             select new { CountyName = c.CountyName, StateName = (co == null)? "": co.StateName };
48.  
49.  
50.             //Same table query
51.             //SELECT [t0].[CountyName]
52.             //FROM [dbo].[County] AS [t0],
53.             //     [dbo].[County] AS [t1]
54.             //WHERE ([t0].[CountyName] = [t1].[CountyName]) AND ([t0].[CountyID] <> [t1].[CountyID])
55.             var counties3 = from c1 in db.Counties
56.                             from c2 in db.Counties
57.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
58.                             select new
59.                             {
60.                                 CountyName = c1.CountyName,
61.                             };
62.  
63.  
64.  
65.  
66.             var counties4 = (from c1 in db.Counties
67.                             from c2 in db.Counties
68.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
69.                             group c1 by c1.CountyName into dups
70.                             select new
71.                             {
72.                                 CountyName = dups.Key,
73.                                 Count = dups.Count()
74.                             }
75.                             ).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

Visual Studio 2005 Add-In: Code to HTML

Ok, this is a very cool tool and time saving add-in for visual studio 2005. Just highlight the code you want to copy to html, right click > copy to html. It does the formatting, color scheme, etc..

http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/

1 class WaterMarkToolTip : ToolTip

2 {

3 public WaterMarkToolTip()

4 {

5 this.OwnerDraw = true;

6 this.Draw += new DrawToolTipEventHandler(OnDraw);

7

8 }

9

10 public WaterMarkToolTip(System.ComponentModel.IContainer Cont)

11 {

12 this.OwnerDraw = true;

13 this.Draw += new DrawToolTipEventHandler(OnDraw);

14 }

15

16 private void OnDraw(object sender, DrawToolTipEventArgs e)

17 {

18 DrawToolTipEventArgs newArgs = new DrawToolTipEventArgs(e.Graphics,

19 e.AssociatedWindow, e.AssociatedControl, e.Bounds, e.ToolTipText,

20 this.BackColor, this.ForeColor, new Font("Arial Unicode MS", 8.25f, FontStyle.Bold));

21 newArgs.DrawBackground();

22 newArgs.DrawBorder();

23 newArgs.DrawText(TextFormatFlags.TextBoxControl);

24 }

25

26

27 }

Some steps when converting Dynamic tsql to Static

Here is a list of things to keep in mind when converting dynamic tsql stored procedures into a static version (for performance enhancement).

1) Remove quotes (carefully). - Going through and eliminated the outside quotes is the easy part. You do have to be careful of the single inner quotes, for example a single quote(s) might be necessary in a varchar statement.

2) Look over the convert statements. - A majority of the convert statements, especially the converting to varchar will need to be looked over and possibly removed in most cases.

3) Replace variables that dealt with parsing. - Some stored procedures might be sending in multiple data into one variable for example @Y = ('1,2,3,4,5') and being accessed like: WHERE x in @Y. This will need to be replaced with either a PATINDEX or CHARINDEX.

4) Remove unnecessary declarations - Some variables are going to end up not being used now, for example the varchar variable that was used to make the dynamic tsql string.



These are the tips, I might remember some more and add them later.