Monday, November 23, 2009

Using an interface to reuse Linq queries

I just read a post about convention based Linq querying and thought I would followup with an alternative method for implementation.

One downside that I see with the implementation of building the expression tree is that the entity may not have an Id property. In this case I would definitely want to see a compile error rather than runtime.

So a simple solution may be to decorate the entities with a marker interface and the place the constraint on the extension method generic, such as

    public interface IEntity
    {}
    
    public partial class Post : IEntity
    {
        // Id property is declared in the Linq to Sql designer file
    }

    public static T GetById<T>(this IQueryable<T> query, int id) where T : class, IEntity
    {
        ...
    }

But why don't we take this a step further and remove a bit of the expression clumsiness? If we were to add an Id property to the interface, then the following would be possible.
    public interface IEntity
    {
        int Id { get; set; }
    }
    
    public partial class Post : IEntity
    {
        // Id property is declared in the Linq to Sql designer file
    }
    
    public static T GetById<T>(this IQueryable<T> queryable, int id) where T : class, IEntity
    {
        return queryable.SingleOrDefault(t => t.Id == id);
    }

This implementation won't compile if the query is on an object that doesn't have an Id. Thus making the interface self-documenting and enforced at compile time.

So as an exercise in understanding expressions this implementation is lacking, but the simplicity is certainly appealing to me.

kick it on DotNetKicks.com Shout it

3 comments:

Frank said...

I've been doing something similar to this for a while, and have run into problems when field names in the designer code do not match the interface.

If "Post", for example, has "PostId" instead of just "Id" as the PK, then you'd need to write wrapper code for PostId. The problem with it is that LINQ to SQL doesn't particularly like it too much unless you start messing with the property's attributes.

[Column(Name = "PostId", Storage = "_PostId", IsDbGenerated = true)]
public int Id
{
get
{
return PostId;
}
set
{
PostId = value;
}
}


When you start using interfaces for tables with other fields that have different names, then you get into more trouble - a regular select query turns out ok, but LINQ generates duplicate fields for an insert/update query and you get a SQL error when this happens.

The only way around it that i've found is to overload the InsertPost/UpdatePost methods in the DataContext and provide your own sql query string, or use stored procs (which i refuse to do).

public partial class MyDataContext
{
partial void InsertPost(Post instance)
{
//Generate SQL query, open connection, and execute.
}

partial void UpdatePost(Post instance)
{
//Generate SQL query, open connection, and execute.
}
}

Would be nice if Microsoft provided an Attribute setting called "DuplicatePropertySoPleaseIgnoreItWhenGeneratingSQLCodeBecauseItsOnlyHereToImplementAnInterfaceKTHXlol1" or something like that...

Have you gone down this far and found a way around it? If so, I'd really like to know, 'cause it's quite painful having to write hand-coded SQL just to get away from having LINQtoSQL creating duplicate field names. I've been searching high and low and have had no luck.

Frank said...

Never mind, found what i needed -

Here's an example of where you'd have "StartDate" defined in an interface, but the DB's field name needs to be mapped to DateFrom.

Use the "Expression" attribute, and life's good!

[Column(Name = "DateFrom", Storage = "_DateFrom", Expression = "DateFrom")]
public DateTime StartDate
{
get
{
return DateFrom;
}
set
{
DateFrom = value;
}
}

Web Development bahrain said...

Web Development bahrain is a team of qualified and experienced Web Developers who are capable of handling projects of any size and can deliver the completed project on time.