Home > .net 3.5, c#, LINQ, Visual Studio 2008 > LINQ Methods – Using LINQ to transform your data

LINQ Methods – Using LINQ to transform your data

August 17, 2007

There are dozens hundreds of posts out on the Internet (and a few even on my blog) regarding simple LINQ queries—select, insert, delete, update, etc.

I spent a bit of time this afternoon familiarizing myself with the wealth of additional methods available.  For a few situations, I thought I’d finally have to break down and hit the tSQL drawing board, but it ended up (at least with Beta 2), the IntelliSense was simply just not as Intelligent as I’d hoped.  I’m sure someone will fix that.

Below, I’ll outline how I use the Distinct, OrderBy, OrderByDescending, GroupBy, and Count methods. 

Creating A Distinct Listing (and Ordering it)

For this example, let’s I have a database and I want to get a list of unique building numbers from that list.  The TSQL would look something like:

SELECT DISTINCT BuildingId FROM Records

Very easy.. from here, we’d simply wrap it up and put it in our business logic.  But, if we wanted to go ahead and use LINQ (since we went to all the trouble to create a LinqToSQL file and learn LINQ), how could we do it?  Simple, the .Distinct() method.

var returns = db.Records.Select(i => i.BuildingId).Distinct();

That’s great, but the trick comes in ordering it.  To order it, we have to pass the int of that selected record back to the compiler.

var returns = db.Records.Select(i => i.BuildingId)

.Distinct()
.OrderBy(i => i);

We can also order by descending…

var returns = db.Records
   .
Select(i => i.BuildingId).Distinct()
   .OrderByDescending(i => i);

Grouping, Counting, Oh My!

I have a nicely ordered list, but say I want to know how many records for each distinct building there is.  The tSQL query looks like:

SELECT DISTINCT BuildingId, Count(RecordId) as Count

FROM Records

GROUP BY BuildingId

ORDER BY Count DESC

Thankfully, we have another couple of methods, GroupBy and CountBy that we can use for this.  It’s important to remember, however, that lambda statements are order of operations sensitive.  If you place the OrderBy before the GroupBy, the GroupBy “resets” the order.  From my experience, Ordering (unless your logic requires it) should be the final method applied.  In addition, the references you create persist RIGHT.  You’ll see below.

 

var returns = db.Records

.GroupBy(i => i.BuildingId)
.Select(i => new { BuildingId = i.Key, Count = i.Count() })
.OrderByDescending(i => i.Count);

[UPDATE: I was emailed for the query expression syntax for the above code, so here ya go!

var returns =

(from record in db.Records

group record by record.BuildingId into rec

orderby rec.Count() descending

select new { Count = rec.Count(), BuildingId = rec.Key });

]

Very cool, but… *twitch*.  In the code snippet above, we get the exact same result as our tSQL statement (though with far more little symbols).  As you read the steps below, remember, we’re moving “left to right” through the statement.  Also, remember that I use ‘i’ arbitrarily, it could be x, y, z, or abracadabra.  It’s simply a variable.

  1. From the table in our data context, group all of the records by BuildingId (specifying “all” because of the lack of a where method),
  2. From that grouping, select out (into a new object) the grouping key (BuildingId) and create a new property called Count that calls .Count() on all records in that grouping,
  3. From that select, order descending by our Count property (which exists as a property, not a method, because we called it that in our select.

For reference, the generated tSQL code looks like:

SELECT [t1].[BuildingId], [t1].[value] AS [Count]

FROM ( SELECT COUNT(*) AS [value], [t0].[SchoolId]

FROM [dbo].[Records] AS [t0]

GROUP BY [t0].[BuildingId] ) AS [t1]

ORDER BY [t1].[value] DESC

A bit over the top on the LINQtoSQL translation, but gets the job done.

Conclusion

As you’ve noticed in most of my LINQ posts—for simply queries, LINQ does not save you lines of code; however, it empowers you to do some of the data transformation within the .NET environment rather than tormenting your DBAs (or yourself, if that’s the case) to create specialized stored procedures or views).  I’d recommend anyone interested in LINQ or at least using data in .NET to go out, research all of the extension methods and data methods available to LINQ and take some of your existing code and try to “translate” it. 

 

  1. YouAreLoved
    April 30, 2008 at 10:48 pm

    Thank you so much for your amazingly awesome examples. You are a genius and I recomend you to everyone. Keep up the good work.

  1. February 20, 2008 at 12:46 pm
Comments are closed.
%d bloggers like this: