Generic L2S Repository

Tweet

I’m sure you’re all tired of me blogging about L2S, but I still like it. It’s just so simple and easy to use. Obviously with .net 4.0, the big issues with L2S seem to be resolved. Alongside l2sprof, LINQ to SQL can be very effective. Anyhow, I thought I’d share a simple generic repository I made.

Here's how you'd use the repository. Basically, you just pass in the DataContext you want to use and then call the method needed. As usual, please let me know if you see any improvements that can be made or if you have any questions.

Building a L2S Expression with .NET 3.5

I recently wanted to reduce some code redundancy by extracting out some code. We had a few helper methods that got results by specific fields.

For example:

public IList<HelpRequest> GetByLastName(string lastName)
{
    return _dc.HelpRequest
        .Where(x=>x.LastName.StartsWith(lastName))
        .OrderBy(y => y.RequestDate).ToList();
}
public IList<HelpRequest> GetByFirstName(string firstName)
{
    return _dc.HelpRequests
        .Where(x => x.FirstName.StartsWith(firstName))
        .OrderBy(y => y.RequestDate).ToList();
}

So obviously there is some redundant code here that we can get rid of and we did. First we created another helper method like this:

public IList<HelpRequest> GetByLastName(string search)
{
    return GetBy(x => x.LastName.StartsWith(search));
}

public IList<HelpRequest> GetByFirstName(string search)
{
    return GetBy(x => x.FirstName.StartsWith(search));
}

private IList<HelpRequest> GetBy(Expression<Func<HelpRequest, bool>> func)
{
    return _dc.HelpRequests
        .Where(func)
        .OrderBy(y => y.RequestDate).ToList();
}

So that got rid of some of the duplication, but not all of it. We’re still doing a StartsWith in the helper and I didn’t want to do that logic there. So…now we have this and this took a long time to figure out…so I’m sure there is a better way because I basically got this working and stopped. Anyhow, here is the latest version, which has more lines of code, but less redundancy. I’m not sure which is better at this point…I’m thinking maybe the first refactoring, but this one is cool too and more flexible I think.

public IList<HelpRequest> GetByLastName(string search)
{
    return GetBy(LastName => search);
}

public IList<HelpRequest> GetByFirstName(string search)
{
    return GetBy(FirstName => search);
}
        
private IList<HelpRequest> GetBy(Func<string, string> func)
{
    return _dc.HelpRequests
        .Where(func.StartsWith<HelpRequest>())
        .OrderBy(y => y.RequestDate)
        .ToList();
}

This one goes along with an extension method that looks like this: (Code below OUTDATED as of 5/9/2010 SEE BELOW)

public static Func<T, bool> StartsWith<T>(this Func<string, string> func)
{
    var searchBy = func.Method.GetParameters()[0].Name;
    var search = Expression.Constant(func(null), typeof(string));

    var searchByParam = Expression.Parameter(typeof(T), searchBy);
    var searchByProp = Expression.Property(searchByParam, searchBy);
    var ignoreCase = Expression.Constant(StringComparison.CurrentCultureIgnoreCase);

    var methodInfo = typeof(string).GetMethod("StartsWith", new[]{typeof(string), typeof(StringComparison)});
    var containsExpression = Expression.Call(searchByProp, methodInfo, search, ignoreCase);

    return Expression.Lambda<Func<T, bool>>(containsExpression, searchByParam).Compile();
}

So, as you can see this little extension method is kinda loaded down. I’m hoping there are some shortcuts to writing these expressions that I don’t know about…but I haven’t found any good examples. I browsed Google for a while and StackOverflow. I even submitted a question…I had one response, but it wasn’t exactly what I was looking for as you can see here. Anyhow, let’s go through this code.

First let’s talk about the GetByLastName & GetByFirstName methods…they’re REALLY simple now, but with one pretty big caveat I think. That caveat is that the property name isn’t strongly typed. If you follow my blog at all,  you know I’m a BIG fan of strongly typed things, but I haven’t found a better way in this instance. So, LastName & FirstName are properties on my HelpRequest object. I used this same method in the “Fun with Lambdas & HtmlHelpers” post. Alrighty, let’s keep going…so the GetBy(Func<string, string>) simply calls the func.StartsWith extension method and returns my IList…now to the meat of this post.

The extension method StartsWith<T> could be put into like 3 lines, but it’s way less readable when you do that…I basically set a variable for every different type of Expression object to keep it a bit readable. So the first thing I do is get my search by, which is my property name. On the next line, I create a constant, which is the right-side of the func passed in. Next on the list is the search by parameter, which sets the type to HelpRequest with the name of searchby (in this case, FirstName or LastName).  After that I set an Expression.Property for the searchBy and then I set another constant for the ignorecase that I’m going to pass into StartsWith in a bit.

Now, I find the method “StartsWith” on a string and setup the parameter definitions of what I want to pass in. In this case, a string and a StringComparison. After this, I call the startsWith on the search property with the search and ignoreCase parameters.

Finally, I return the lambda expression that is required for my Where on my LINQ to SQL statement. I also found that you have to call the Compile in order for it to work. What I do like about this extension method is that I can now use it for all my searches. Of course I’m going to continue testing it and verifying that there’s not a better way, but for now, I think it’ll work.

Please let me know if you have any suggestions or comments.

Thanks for reading!

UPDATE: 5/9/2010

So thanks to Brad commenting on using Ants or SQL Profiler, I did a little more investigating on the code above. Basically what was happening was this:

SELECT [t0].[FirstName], [t0].[LastName]
FROM [hd].[HelpRequest] AS [t0]

So, I made some adjustments to the static StartsWith<T> method and now it looks like this:

private static Expression<Func<T, bool>> StartsWith<T>(Func<string, string> func)
{
    var searchBy = func.Method.GetParameters()[0].Name;
    var search = Expression.Constant(func(null), typeof(string));

    var searchByParam = Expression.Parameter(typeof(T), searchBy);
    var searchByExp = Expression.Property(searchByParam, searchBy);
    //var ignoreCase = Expression.Constant(StringComparison.CurrentCultureIgnoreCase);

    var methodInfo = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });//, typeof(StringComparison)});
    var containsExpression = Expression.Call(searchByExp, methodInfo, search);//, ignoreCase);

    return Expression.Lambda<Func<T, bool>>(containsExpression, searchByParam);//.Compile();
}

First thing to note is the return type has been changed from Func<T, bool> to Expression<Func<T, bool>>. This allows us to do away with the Compile() at the bottom of the method. I was also able to do away with the ignoreCase and StringComparison stuff because it’s converted to SQL and in this case we’re not caring about case-sensitvitity. So now my SQL looks like this:

exec sp_executesql N'SELECT [t0].[FirstName], [t0].[LastName]
FROM [hd].[HelpRequest] AS [t0]
WHERE [t0].[FirstName] LIKE @p0',N'@p0 varchar(7)',@p0='deran %'

So, this is a good thing. Thank you Brad! Please let me know if you see anything else that could cause a problem.

Playing with C# Func<T, TResult> and ADO.NET

I’ve always liked the look of the generic Func and I think the possibilities behind it are awesome. However, it’s usually difficult for me to find a good place to use it. I’m probably just not that used to using it yet and I am missing out on some huge advantages. Ignorance is not bliss sometimes :)

So anyhow, I was playing with the good ole ExecuteScalar & ExecuteNonQuery commands on the IDbCommand interface and came up with something kinda cool. At least I thought so…

I tend to use a DbHelper when I’m doing easy things instead of using NHibernate, Linq2Sql, etc, etc. Well here’s what I started with:

public class DatabaseHelper
{
public static T ExecuteScalar<T>(string connstring, string sql, params IDataParameter[] parameters)
{
    using (var oConn = new SqlConnection(connstring))
    {
        oConn.Open();
        using (var oCmd = new SqlCommand(sql, oConn))
        {
            oCmd.Parameters.AddRange(parameters);
            return (T) oCmd.ExecuteScalar();
        }
    }
}

public static void ExecuteNonQuery(string connstring, string sql, params IDataParameter[] parameters)
{
    using (var oConn = new SqlConnection(connstring))
    {
        oConn.Open();
        using (var oCmd = new SqlCommand(sql, oConn))
        {
            oCmd.Parameters.AddRange(parameters);
            oCmd.ExecuteNonQuery();
        }
    }
}
}

Obviously not pretty and a TON of duplicated code. VERY SMELLY!

So the first thing I did was create a DatabaseCriteria class to help with the number of params I’m passing and it looks like this:

public class DatabaseCriteria
{
    public DatabaseCriteria(string connstring, string sqlStatement, params IDataParameter[] parameters)
    {
        ConnectionString = connstring;
        SqlStatement = sqlStatement;
        Parameters = parameters;
    }
    public string ConnectionString { get; set; }
    public string SqlStatement { get; set; }
    public IDataParameter[] Parameters { get; set; }
}

That’s a little better and it’s easy to modify the existing code that calls my helpers. The next thing I did was create small factories for my connection & command so I wasn’t bound to the Sql Server version and I could easily swap out databases. The builders look like this:

private static IDbConnection BuildConnection(string connstring)
{
    return new SqlConnection(connstring);
}

private static IDbCommand BuildCommand(string sqlStatement, IDataParameter[] parameters, IDbConnection connection)
{
    var command = new SqlCommand(sqlStatement, (SqlConnection)connection);
    command.Parameters.AddRange(parameters);
    return command;
}

Obviously I’m using some SqlCommand features like the AddRange and I’m casting straight to the SqlConnection, but that code is only in one spot and it’d be easy to setup an IoC tool or something. I want to keep this blog simple though, so I’ll leave it as is for now.

Okay, so now let’s get to the fun stuff! Here’s my new ExecuteSql generic method:

private static T ExecuteSql<T>(DatabaseCriteria dbCriteria, Func<IDbCommand, T> func)
{
    using (var oConn = BuildConnection(dbCriteria.ConnectionString))
    {
        oConn.Open();
        using (var oCmd = BuildCommand(dbCriteria.SqlStatement, dbCriteria.Parameters, oConn))
        {
            return func.Invoke(oCmd);
        }
    }
}

Okay, so this accepts a DatabaseCriteria object and a generic Func that invokes an action on IDbCommand and returns T. It also takes advantage of the two factories I made and obviously the using statement.

Updated: 1/26/2010 8:16PM
Thanks to the comments of Thilak Nathen, I’ve made an improvement to the ExecuteScalar<T> method. Before him bringing it to my attention, my code could’ve thrown the dreaded “Object reference not set to an instance of an object” message. I think we all have grown to hate that message. Sadly it’s easy to check for and we sometimes just look over it. If you’re interested, the ExecuteScalar before the update was just one line and it was: return ExecuteSql(dbCriteria, x => (T) x.ExecuteScalar());

Thanks Thilak!

Now my DatabaseHelper looks like this:

public class DatabaseHelper
{
    public static T ExecuteScalar<T>(DatabaseCriteria dbCriteria)
    {
        var result = ExecuteSql(dbCriteria, x => x.ExecuteScalar());
        return (result is T) ? (T)result : default(T);
    }

    public static void ExecuteNonQuery(DatabaseCriteria dbCriteria)
    {
        ExecuteSql(dbCriteria, x => x.ExecuteNonQuery());
    }

    private static T ExecuteSql<T>(DatabaseCriteria dbCriteria, Func<IDbCommand, T> func)
    {
        using (var oConn = BuildConnection(dbCriteria.ConnectionString))
        {
            oConn.Open();
            using (var oCmd = BuildCommand(dbCriteria.SqlStatement, dbCriteria.Parameters, oConn))
            {
                return func.Invoke(oCmd);
            }
        }
    }

    private static IDbConnection BuildConnection(string connstring)
    {
        return new SqlConnection(connstring);
    }

    private static IDbCommand BuildCommand(string sqlStatement, IDataParameter[] parameters, IDbConnection connection)
    {
        var command = new SqlCommand(sqlStatement, (SqlConnection)connection);
        command.Parameters.AddRange(parameters);
        return command;
    }
}

I think there could be a little more clean up here, but basically I think it’s pretty clean. There is pretty much zero code duplication and no dependencies on implementations other than in my two builders. You should also notice that everything is private other than the ExecuteScalar and the ExecuteNonQuery because I only want people to use those two methods. Anyhow, I like it! Hopefully you do too.

Thanks for reading!