LINQ .Net

Hello All,

I have just been doing some .Net work using LINQ.  Linq is Microsoft's new very clever ORM.  It does some amazing things, and most of the time what you want.  The other times you tear your hair out.

The magic is that this C# looking code actually gets converted to (quite different) SQL.  But of course, SQL Server is different in many subtle ways to the .Net engine.

It confirms my idea that simple queries can be automated simply (like SimpleORM), and that complex queries are much better written in the native SQL that you are dealing with.

Here is an example of what LINQ can do.  Adding the simple term a.PublishedDate > date mysteriously killed my performance.



        var articlesWithEntity = (from p in dc.Entities
                        where p.EntityName == EntityName
                        select p.Article).Distinct();

        var uniqueArticleIds = from p in articlesWithEntity
                            group p by p.Title // SQL cannot compare TEXT type in p.Description:new { p.Title, p.Description };
                                into g
                                select g.Max(e => e.ID);

        var uniqueArticles = dc.Articles.Where(a => uniqueArticleIds.Contains(a.ID));

        var theArticles2 = from p in uniqueArticles orderby p.Title
                          select new {
                              p.ID, p.Title, p.PublishedDate,
                              FeedTitle = p.Feed.Title, p.Description, 
                              Url = p.Url.Replace("/", "/ ").Replace("&", " &")
                          };

        var theArticles3 = theArticles2.Where(a => a.PublishedDate > date);

Results in

SELECT [t2].[ID], [t2].[Title], [t2].[PublishedDate], [t2].[Title2] AS [FeedTitle], [t2].[Description], [t2].[value] AS [Url]
FROM (
    SELECT [t0].[ID], [t0].[Title], [t0].[PublishedDate], [t1].[Title] AS [Title2], [t0].[Description], REPLACE(REPLACE([t0].[Url], @p0, @p1), @p2, @p3) AS [value]
    FROM [dbo].[Articles] AS [t0]
    INNER JOIN [dbo].[Feeds] AS [t1] ON [t1].[ID] = [t0].[Source]
    ) AS [t2]
WHERE ([t2].[PublishedDate] > @p4) AND (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT MAX([t5].[ID]) AS [value]
        FROM (
            SELECT DISTINCT [t4].[ID], [t4].[Title], [t4].[Url], CONVERT(NVarChar(MAX),[t4].[Description]) AS [Description], [t4].[Source], [t4].[PublishedDate], [t4].[HasCalais]
            FROM [dbo].[Entities] AS [t3]
            INNER JOIN [dbo].[Articles] AS [t4] ON [t4].[ID] = [t3].[ArticleID]
            WHERE [t3].[EntityName] = @p5
            ) AS [t5]
        GROUP BY [t5].[Title]
        ) AS [t6]
    WHERE [t6].[value] = [t2].[ID]
    ))
ORDER BY [t2].[Title]

Wow!  

(Certainly SQL could be improved -- I should be able to write table.fkey.fkey.column without setting up subselects of joins.  And the Let clause that lets complex queries be split up.  Those are the main advantages of LINQ.)

Otherwise, LINQ avoids Hibernate's byte code generation by using C# Properties to call methods directly, together with a code generator.  That part feels much better, but is much more complex than SimpleORM for little benefit.

Anthony

Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
Just because it is possible to push twigs along the ground with ones nose
does not necessarily mean that is the best way to collect firewood.



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    mailto:[email protected] 
    mailto:[email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/