Archive for category Entity Framework

Using Entity Framework to run SQL queries

At work we are using Entity Framework, and we have some reports that we were using that were taking a long time to run. The culprit was the Select N+1 monster, which was rearing its ugly head, really 4 or 5 of them, as we were having the Select N+1 problem that many levels deep, practically loading the whole database.

In working to solve this I created some views in the database, with some SQL that was going to need to be run against those views from the codebase. This is because it may be more like a table function, but I still have to get with our DBA to find the gaps in my solution, but in the mean time I had a SQL statement that I need to run from the application, and did not want to get into pure ADO.NET and having to transform data records into the objects I was wanting to return. I found a solution from Craig Stuntz, and applied that to our code base.

I have my code starting from the DbContext, as opposed to just the ObjectContext, and have not done anything to think about SQL Injection attack in this example.

public class ColumnSummary
{
    public string ColumnName { get; set; }
    public bool IsNullable { get; set; }
    public int? MaxLength { get; set; }
}


public IEnumerable<ColumnSummary> GetColumnsForTable(string tableName)
{
    string sql = @"
select Column_Name as ColumnName, Is_Nullable as IsNullable, Character_Maximum_Length as MaxLength
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tableName";

    var context = ((IObjectContextAdapter)_dbContext).ObjectContext;
    return context.ExecuteStoreQuery<ColumnSummary>(sql, new SqlParameter("@tableName", tableName));
}

This allows me to run SQL, and not have to worry about handling the mapping myself, but just let the Entity Framework data access layers handle that for me, as long as I give it something that matches the results set.

And if you need to do this in multiple places, I would recommend creating some sort of SQL Query object, which would take in the SQL statement you would like to run and the SQLParameters to pass to ExecuteStoreQuery, and let the SQL Query object hide the details of the how the query gets run, and have those details behind a walled garden.

–Proctor

Advertisements

, , ,

2 Comments