LINQ, GroupJoin, and Left Outer Joins

Filed in Software Developement Leave a comment

This is a topic which involves me scouring the internet every time I need to do this task. outer joins in LINQ are one of the few LINQ concepts that is not very simple to execute.

Essentially, an Outer Join maps to GroupJoin. GroupJoin is almost identical to Join, except that your right side lambda parameter is a collection (of potentially zero items) instead of an object reference. Because it is a collection it doesn’t really help you out if you need to do a real left outer join. To solve this we make use of SelectMany and DefaultIfEmpty LINQ methods.

SelectMany will allow us to expand the collection, and DefaultIfEmpty will retain the empty collections, together we can supply a value of null (or whatever you want) to a column that is not joined. Below is an example to illustrate.

Customers
    .GroupJoin(Orders, x => x.CustomerID, x => x.CustomerID, (a, b) => new { a.Name, b })
    .SelectMany(x => x.b.DefaultIfEmpty(), (a, b) => a.Name, OrderID = (b == null ? (int?)null : b.OrderID)

, , , ,

LINQ(-to-SQL), DateTimeOffset, and Time Zones

Filed in Software Developement Leave a comment

Today I was requested to produce a report on some timestamped data, but with the timestamps in the time zone where the data was collected.  Now long ago I learned the valuable lesson of using DateTimeOffset to store data and there is no change with this scenario, but something I had never actually tried before was getting LINQ-to-SQL (LINQPad) to emit timestamp data in a time zone other than my current system time zone.  This is really more of a general .NET solution to the same problem as i don’t believe LINQ-to-SQL actually converts any timestamp data on the data side (nor should it, we only want the DateTimeOffset values).

The crucial type in this case is TimeZoneInfo. First you need to figure out which time zone you want as your target (destination) time zone.  Use TimeZoneInfo.GetSystemTimeZones() to figure that one out, and make note of the Id property.  Now you need to create the proper TimeZoneInfo instance, you could use the results of that list, but more simply you can create the instance from the Id property using TimeZoneInfo.FindSystemTimeZoneById("Id") or for a real world example: TimeZoneInfo.FindSystemTimeZoneById("Newfoundland Standard Time"). The last part is to use TimeZoneInfo.ConvertTime(DateTimeOffset, TimeZoneInfo) to offset your DateTimeOffset to the proper amount.

Here is a full example:

TimeStampedData.Select(x => new { x.Name, Timestamp = TimeZoneInfo.ConvertTime(x.Timestamp, TimeZoneInfo.FindSystemTimeZoneById("Newfoundland Standard Time")) })

Nice and simple, but a little bit of a pain to actually get the right time zone.  Ideally, .NET would have static accessors to all the common standard time zones (as TimeZoneInfo static instances), but otherwise this is a reasonably easy solution to achieve.

, , , ,

Important note when updating LINQ-to-SQL view fields

Filed in Software Developement Leave a comment

I am working on a complex project right now where I am using LINQ-to-SQL with some complicated views. I need to at some times updates some of the fields on these views. I know this is possible because you can achieve this in SSMS (note that you can only update fields which already exist, NULL outer join fields will not update since they don’t actually exist). Anyways, I would get my row via the view from the data context, then update the property corresponding to the field. But when I called SubmitChanges() nothing would happen, the database would not be updated.  So to debug I would attach the data context logger to Console.Out so I could visualize why it wasn’t working. The log was empty on the SubmitChanges() call, which was troubling.

The solution to this problem is to make sure your views have a Primary Key field set. By default your views won’t have this property set because it’s ambiguous on a view. But you can specify this in the property editor when the field is highlighted. If your view doesn’t have the equivalent of a primary key then you are SOL because you cannot update a view field without a PK.  Once I specified the PK on the view, the updates started working.

This behaviour, while it make perfect sense, is simply not obvious and very difficult to track down if you don’t realize what the problem is. It’s too bad there isn’t some sort of warning from LINQ-to-SQL when you generate a view with no PK field set.  Anyways, simple solution to an otherwise difficult to track down problem.

, , , ,

TOP