List of unique strings in database table using Linq?

Question

I have a "Tickets" table with somewhat following structure (removed unnecessary columns)

int | string   | int   |
ID  | Window   | Count |
------------------------
0   | Internet | 10    |
1   | Phone    | 20    |
2   | Fax      | 15    |
3   | Fax      | 10    |
4   | Internet | 5     |
.   | .        | .     |
.   | .        | .     |

And I have mapped this table to a class "Ticket". So I can get all records like this:

var tickets = from t in db.Tickets
              select t;

Now I need to get the list of unique window names in the table. For above table, list would look something like:

• Internet
• Phone
• Fax

Is there anyway to create this list without fetching all records and iterating over them?

I am using SQL Server 2008 express edition.

EDIT: Thanks for the answers guys it solved the above problem. Just being greedy but is there any way to also get the total of count for each window. For example:

• Internet = 15
• Phone = 25
• Fax = 20

Answer 1

Linq Samples Part 11 by Bill Wagner should help you. Just call the Distinct() function on your Linq result. It's as simple as that.

var tickets = (from t in db.Tickets
               select t).Distinct();

[EDIT]

Concering the numbers of the occurences, see this example as a hint.

        int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 5 };

        var numberGroups =
            from n in numbers
            group n by 5 into g
            select g;

        g.Count(); // occurences

Answer 2

How about:

var tickets = db.Tickets.Select(t => t.Window).Distinct();

I prefer to only use query expressions when I'm doing more than one operation, but if you like them the equivalent is:

var tickets = (from t in db.Tickets
               select t.Window).Distinct();

To get the counts, you need to group:

var tickets = from t in db.Tickets
              group t by t.Window into grouped
              select new { Window=grouped.Key, 
                           Total=grouped.Sum(x => x.Count) };

foreach (var entry in tickets)
{
    Console.WriteLine("{0}: {1}", entry.Window, entry.Total);
}

Note that this should all end up being performed at the database side - examine the SQL query to check this.

Answer 3

You can use the .Distinct() operator - it'll make a SELECT DISTINCT to the database, giving exactly what you ask for.

Answer 4

The query will be evaluated inside the store.

var windows = db.Tickets.Select(ticket => ticket.Window).Distinct();

Answer 5

 var query2 = from ticket in db.tickets 

 group window by ticket.Window into result
 select new
 {
     Name = result.Window,
     Sum = result.Sum(i => i.Count)
 };