cameronfletcher.com

random thoughts and discussions on the things that interest me

Converting a CTE T-SQL Statement into Linq2Sql

Below is the SQL statement including the common table expression that I need to re-write using Linq2Sql for an application I’m working on. The query identifies the subset of most up-to-date records using a group by query in a CTE. It then performs a join back to the booking table to return the full list of most up-to-date records.What is noticeable about this particular piece of code is the join between table and CTE making use of the isnull keyword.

with cte (RootBookingID, CreatedTime)
as
(
    select
        isnull(ParentBookingID, BookingID),
        max(CreatedTime) -- get the latset version
    from [system].Booking
    where Deleted = 0
    group by isnull(ParentBookingID, BookingID)
)

select b.*
from [system].Booking b inner join cte
on cte.RootBookingID = isnull(b.ParentBookingID, b.BookingID)
    and cte.CreatedTime = b.CreatedTime;

When rewriting this in C#, we first declare the query for the CTE as its own variable.

//define the cte to use as an anchor
var cte = from b in this.Model.Bookings
    where b.Deleted == false
    group b by b.ParentBookingID ?? b.ID into g
    select new
    {
        BookingID = g.Key,
        CreatedTime = g.Max(b => b.CreatedTime)
    };

We can then reuse this variable within our core query (below). Notice how the join on isnull is created by explicitly defining the name of the anonymous type.

//perform a join on the cte to get the results
var qry = from b in this.Model.Bookings
    join c in cte
        on new
        {
            ID = (b.ParentBookingID ?? b.ID),
            b.CreatedTime
        } equals new
        {
            ID = c.BookingID,
            c.CreatedTime
        }
    order by b.PickUpTime
    select b;

The post here explains the stumbling blocks I encountered:

To join multi-valued keys you need to construct an anonymous type on both sides of the ‘equals’ that is the same type. The anonymous type initializer expression infers both type and name of members from the expressions that are supplied. Using the name = value syntax in the initializer you can specify the name the compiler uses when creating the type. If all members, types, and names are the same then the anonymous types are the same type.

An error was encountered. Please return to the previous page and try again.

If you are trying to use the ASP.NET website administration tool and are getting a web page stating “An error was encountered. Please return to the previous page and try again.” it is possible that you have the same error as me (see below). Another symptom is clicking on the “How do I use this tool?” link and being presented with a message stating “Tool Has Timed Out”.

asp_error

It would appear that Microsoft, in its wisdom, has developed a tool that cannot handle the path of the web application it is designed to administer containing non-standard characters. Or spaces. To fix, simply copy your solution to a path that doesn’t contain any non-standard characters. Or spaces.

eg. Changing C:\Will (Not.) Work\see.sln to C:\WillWork\see.sln

It’s an easy fix to a problem most likely created by a tool both from, and within, Microsoft.

Internal Interfaces

Occasionally there is the need to expose, for the purposes of an API or such like, a property of a class that has different external and internal implementations. Consider the code below: a person class with an Id property that is set to -1 by default with an external get property and an internal set property.

public class Person
{
    private int _id = -1; // default value

    public int Id
    {
        get { return this._id; }
        internal set { this._id = value; }
    }
}

If there are several classes that implement the Id property in this manner and there is a need to reference them through a common interface externally then implementing the following on each of the classes should be sufficient:

public interface IIdentifiable
{
    int Id { get; }
}

However, to reference these classes internally using the same common interface may be insufficient as it does not allow a call to the internal set property. Clearly it would not be efficient, or necessarily easy, to cast the classes to their individual types prior to making the call. One solution is to use a separate interface for internal operations; an interface with internal scope:

internal interface IIdentifiableInternal : IIdentifiable
{
    new int Id { get; set; }
}

Here, we implement the IIdentifiable interface and declare the Id property as new, effectively hiding its IIdentifiable implementation. We don’t reflect this in quite the same manner in our implementation of the IIdentifiableInternal interface on our class though. Instead, we explicitly implement the IIdentifiableInternal interface:

public class Person : IIdentifiableInternal
{
    private int _id = -1; // default value

    public int Id
    {
        get { return this._id; }
    }

    int IIdentifiableInternal.Id
    {
        get { return this._id; }
        set { this._id = value; }
    }
}

The external members of the class now include the public get method of the Id property, as does the public IIdentifiable interface. However, internally the class can be referenced using the IIdentifiableInternal interface which allows access to both the get and set methods of the property.

What has been demonstrated is how to take a number of classes with common members that have both public and internal scope and provide a means to reference them through a common interface, both internally and externally.

As a final point, if you have a linked library which you want to have access to the internal interfaces then you can use the following assembly attribute to expose internal to the specified assembly:

[assembly: InternalsVisibleTo("MyLibrary.InterfaceExample")]

Overridden method OnMeasureItem is not being invoked

I just ran into an issue when subclassing the ComboBox class whereby the OnMeasureItem method that I was overriding was not being called. I had set the DrawMode to OwnerDrawFixed in the constructor for my ComboBox which had in turn filtered through to the designer (non-default value). When I amended the value in the constructor to OwnerDrawVariable the change did not then filter through to the designer (no surprises there). The result being that my constructor had the following code:

this.DrawMode = DrawMode.OwnerDrawVariable;

However, this was clearly at odds with the designer. So in order to stop this occurring again, and to stop any modification of the DrawMode via the designer, I changed the line in the constructor of my ComboBox to reference the base like so:

base.DrawMode = DrawMode.OwnerDrawVariable;

I then added the DrawMode property with the new keyword to replace the base version (see below). You need to include the property setter as the designer will still try to assign a value to DrawMode.

[Browsable(false)]
public new DrawMode DrawMode
{
    get { return base.DrawMode; }
    set { }
}

After tidying up the designer code everything works like a dream.

« Newer Posts