LINQ Queries Follow-up

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.