When I was writing my review of Visual Studio 2008, I built a little console application that generated JavaScript data structures from a SQL Server 2000 database in C#, using LINQ to SQL. As I mentioned in the review, I never did figure out how to write the LINQ equivalent of a SQL "SELECT DISTINCT" query.

In reading the LINQ section of C# 3.0 in a Nutshell*, I have come to realize that the LINQ code I wrote for that little project was basically SQL translated literally to LINQ. I might have done it quite differently if I had been thinking directly in LINQ.

Here's the meat of the program:

using(pcpDataClasses1DataContext db=new pcpDataClasses1DataContext())
{
    //Generate AVInfo
    string category1 = "AntiVirus";
    string category2 = "AntiSpyware";
    bool init = false;
    var qAV = from ai in db.AppInfos
              join acai in db.AppCatAIs on ai.Prodid equals acai.Prodid
              join ac in db.AppCategories on acai.acatid equals ac.acatid
              where ac.Category == category1 || ac.Category== category2
              orderby ai.ProductName
              select ai;
    //Beginning of JS structure
    Console.WriteLine("var AMInfo = [");
    //Per-item entries
    string fmt = "{{\nProdid: {0},\nName: \"{1}\",\nInst: [ {2} ],\nRoot: \"{3}\",\nSigs: \"{4}\",\nInit: {5},\nNeedRoot: {6}\n}}";
    foreach (AppInfo a in qAV)
    {
        if (init)
            Console.Write(",");
        Console.WriteLine(fmt, a.Prodid, a.ProductName, a.InstPattern, a.Root, a.Sigs,
            String.IsNullOrEmpty(a.InitMethod) ? "null" : a.InitMethod, a.NeedRoot.ToString().ToLower());
        if (!init)
            init = true;
    }
    //finish it off
    Console.WriteLine("]");
}

The O-R diagram for the database tables being queried is here.

How would you rewrite this to (1) emit only distinct items, and (2) be more like idiomatic LINQ and less like translated SQL?

*In my posting on Books for Learning C# 3.0 I said that I didn't yet have a copy of the C# 3.0 Nutshell book. I'm embarrassed to admit that I did have it: it was hiding in a big stack of books.

Posted by Martin Heller on January 28, 2008 08:39 AM