Tuesday, September 30, 2008

From SQL to LINQ in C#

I am currently working on a new project using the latest technologies from Microsoft. Although most of the technologies are still in Beta, one of the languages I had to learn was LINQ. Coming from a SQL background, I could honestly say that the step from SQL to LINQ was a bit daunting. Statements that I wrote in SQL within 2 minutes took me over an hour to figure out in LINQ to start with. Below is a outline of converting SQL thinking to LINQ thinking that became the fundamental driver behind writing all my LINQ statements now.

For example, using this framework I could easily translate the following SQL call into LINQ syntax quite easily :

SQL Statement :

DECLARE @lat float
DECLARE @lng float

SET @lat = 40.66666666666
SET @lng = -3.54434333344

SELECT     b.BlockId, b.PolygonId
FROM         Block AS b INNER JOIN
                      Polygon AS p ON b.PolygonId = p.PolygonId INNER JOIN
                      Point AS po ON p.PolygonId = po.PolygonId
GROUP BY b.BlockId, b.PolygonId
HAVING (MIN(po.Latitude) < @lat) AND  (MAX(po.Latitude) > @lat) AND (MIN(po.Longitude) < @lng) AND  (MAX(po.Longitude) > @lng)

LINQ Statement : 

Entities Context = ContextManager.Instance.GetContext();
            var Query = from B in Context.Block
                                  where B.Polygon.Point.Min(point => point.Latitude) <>
  && B.Polygon.Point.Max(point => point.Latitude) > Latitude 
  && B.Polygon.Point.Min(point => point.Longitude) <>
  && B.Polygon.Point.Max(point => point.Longitude) > Longtitude
                          select B;

            return Query.FirstOrDefault();

As long as you follow the structure it seems as though most SQL statements can be easily translated to LINQ this way. One thing to note, is that I am using LINQ to Entities (Objects), and there are certain limitiation/bugs that I have encountered with using the "FirstOrDefault()" method when deailing with LINQ to SQL.

Hope this helps, 

 - Tim

No comments: