Gabriel Bogéa Perez

The title of the post is actually misleading. A better title (although too long) would be: Migrating the Repository concept used in the MVC Storefront to the Entity Framework.

Lately I’ve been playing more with the Entity Framework. The other day I was trying to replicate the way Rob Conery used the Repository Pattern in the MVC Storefront sample application and I was surprised to learn that the Entity Framework has some differences from LINQ to SQL that I did not expect.

My first test was very simple. I wanted to get an IQueryable of Posts with it’s respective Comments attached. The StoreFront did this in a really clever and simple manner. The following code was supposed to work:

class BlogRepository
{
    SOEntities ctx = new SOEntities();

    public IQueryable<Comment> GetComments()
    {
        return from comment in ctx.Comments.Include("Post")
               select new Comment()
               {
                   CommentId = comment.CommentId,
                   Author = comment.Author,
                   Body = comment.Body,
                   PostId = comment.Post.PostId
               };
    }

    public IQueryable<Post> GetPosts()
    {
        var posts = from post in ctx.Posts
                    let comments = GetComments()
                    select new Post()
                           {
                               PostId = post.PostId,
                               Title = post.Title,
                               Body = post.Body,
                               Comments = comments.Where(x => x.PostId == post.PostId)
                           };
        return posts;
    }
}

class Post
{
    public Guid PostId { get; set; }

    public string Title { get; set; }

    public string Body { get; set; }

    public IList<Comment> Comments { get; set; }
}

class Comment
{
    public Guid CommentId { get; set; }

    public Guid PostId { get; set; }

    public string Author { get; set; }

    public string Body { get; set; }

}

class Program 
{
static void Main(string[] args)
{
    BlogRepository rep = new BlogRepository();
    var posts = rep.GetPosts();
    posts = posts.Where(x => x.Title.Contains("MVC"));
    List<Post> postList = posts.ToList();
}
}

I get an error saying that Linq to Entities does not recognize the method GetComments().

It’s not a bug, it’s is just the way the EF works. The method GetComments can’t be translated to anything in the provider. But why does it work with Linq to Sql you ask? Because it converts the Linq expression in a different way. Sorry but the post would be too long if I got into this subject.

The way recommended by the EF team is you use the AsEnumerable() extension method, forcing the initial query to be executed and then you would be working with objects. Here is how I needed to change my code for this to work:

public IQueryable<Post> GetPosts()
{
    var posts = from post in ctx.Posts.AsEnumerable()
                let comments = GetComments()
                select new Post()
                       {
                           PostId = post.PostId,
                           Title = post.Title,
                           Body = post.Body,
                           Comments = comments.Where(x => x.PostId == post.PostId)
                       };
    return posts.AsQueryable();
}

This does work, but when ToList() method is called in the posts object, instead of filtering the posts in the sql code, all the posts are brought from the database and then they are filtered as objects. This is a work around but it’s not ideal.

I have another work around, it also isn’t the ideal but it does work in the sense that it will apply the filter in the database.

public IQueryable<Post> GetPosts()
{
    var comments = GetComments();
    var posts = from post in ctx.Posts
                select new Post()
                       {
                           PostId = post.PostId,
                           Title = post.Title,
                           Body = post.Body,
                           CommentsQry = comments.Where(x => x.PostId == post.PostId)
                       };
    return posts;
}

class Post
{
    public Guid PostId { get; set; }

    public string Title { get; set; }

    public string Body { get; set; }

    public IList<Comment> Comments { get; set; }

    public IQueryable<Comment> CommentsQry
    {
        set { Comments = new LazyList<Comment>(value); }
    }
}

The trick is that I introduced a IQueryable property in the Post class. This property receives an IQueryable and under the hood converts it to a LazyList and sets the Comments property. Doing this overcomes the limitation that the EF has in not letting you use constructors that take parameters in the EF code. It only allows parameterless constructors on the select projection.

If you run this code you will see that the records are filtered in the database. The reason I still don’t consider this solution ideal is that I had to make a change to my model class and created a property that is not related to the business but rather to a limitation imposed by the framework. I still don’t see a way around this. If anyone has any suggestions please do leave them as comments.

A very interesting post was written by Muhammad Mosa a while ago. It’s worth visiting his blog to checkout his idea.

I have a group of classes that I build to get metadata from the database to use with my code generators. The class has the following structure:

public class ConstraintMetadata
{
    public string ConstraintName { get; set; }

    public string ColumnName { get; set; }

    public string ConstraintType { get; set; }

    public string TableName { get; set; }
}

I wanted to write a group by clause using Linq to group the objects I retrieved from the db by TableName, ConstraintName and ConstraintType. This would allow me to have one object for each constraint with a list of all the columns. Since Linq only allows one object in the group by clause the way out is to create an anonymous object with all the properties I want.

var constraints = from c in constraintsMetadata
                  group c by new
                             {
                                 c.TableName, 
                                 c.ConstraintType, 
                                 c.ConstraintName
                             } into g
                      select new
                      {
                          g.Key.ConstraintName,
                          g.Key.ConstraintType,
                          g.Key.TableName,
                          Columns = g.Select(x=>x.ColumnName).ToList()
                      };

Now, if I want to get all the constraints for a table and print it to the console window I can do this:

var tableConstraints = constraints.Where(x => x.TableName == table.Name);

foreach (var tableConstraint in tableConstraints)
{
    Console.Out.WriteLine("Constraint Name:{0}, Table:{1}, Type:{2} Columns:", 
        tableConstraint.ConstraintName , 
        tableConstraint.TableName, 
        tableConstraint.ConstraintType);
    foreach (var column in tableConstraint.Columns)
    {
        Console.Out.WriteLine(column);
    }
}

When you query a DataTable using Linq the normal result will be an IEnumerable, which is not a DataTable (obviously). If you need a DataTable as a result you already have an extension method called CopyToDataTable which will turn your IEnumerable into a new DataTable. Although if you want to join two DataTables and the merge the DataRows into a new Anonymous Type the CopyToDataTable will not help you. Let’s see how to create a helper method that will transform an IEnumerable of anonymous types into a new DataTable.

First let’s create two DataTables: Person and Job. Here’s the code:

DataTable dtPerson;
DataTable dtJob;

dtPerson= new DataTable("Person");
DataColumn dc;

dc = new DataColumn("Id", typeof(int));
dtPerson.Columns.Add(dc);

dc = new DataColumn("Name", typeof(string));
dtPerson.Columns.Add(dc);

dc = new DataColumn("Age", typeof(int));
dtPerson.Columns.Add(dc);

//JOB DataTable
dtJob = new DataTable("Job");

dc = new DataColumn("PersonId", typeof(int));
dtJob.Columns.Add(dc);

dc = new DataColumn("Position", typeof(string));
dtJob.Columns.Add(dc);

Here is the code to populate the DataTables:

DataRow dr = dtPerson.NewRow();
dr[0] = 1;
dr[1] = "Gabriel";
dr[2] = 31;
dtPerson.Rows.Add(dr);

dr = dtPerson.NewRow();
dr[0] = 2;
dr[1] = "Elisa";
dr[2] = 27;
dtPerson.Rows.Add(dr);

dr = dtJob.NewRow();
dr[0] = 1;
dr[1] = "Programmer";
dtJob.Rows.Add(dr);

dr = dtJob.NewRow();
dr[0] = 2;
dr[1] = "Manager";
dtJob.Rows.Add(dr);

Now let’s write a Linq query to join these two DataTables:

var query = from p in dtPerson.AsEnumerable()
             join j in dtJob.AsEnumerable() on p.Field<int>("Id") equals j.Field<int>("PersonId")
             select new
             {
                 Id = p.Field<int>("Id"),
                 Name = p.Field<string>("Name"),
                 Job = j.Field<string>("Position")
             };

Notice that the result of the query is an IEnumerable of an anonymous type with three properties (Id, Name and Position) which is a merge of our two DataTables data. Now for the helper method that will convert this result to a new DataTable. The base of this idea is in my previous article on using reflection on anonymous types. I’ll use this principle to inspect the properties of the anonymous type and create new DataColumns for the new DataTable. After that all it’s needed is iterating over the results creating new DataRows. Here’s the method:

public static DataTable ToDataTable(this IEnumerable objectList)
{
    //if the result is null or if the number of
    //objects is less then 1, return null
    if (objectList == null || 
        objectList.OfType<object>().Count() < 1)
    {
        return null;
    }
    //create a new list based on the IEnumerable
    List<object> list = new List<object>(objectList.OfType<object>());

    //take the first object in the list
    object o = list[0];
    //get the type of the object
    Type t = o.GetType();
    //read all the info of the properties
    PropertyInfo[] properties = t.GetProperties();

    DataTable dt = new DataTable();
    //for each property create a new column
    //in the DataTable
    foreach (var pi in properties)
    {
        //the new column has the name of the property
        //and it's type
        DataColumn dc = new DataColumn(pi.Name, pi.PropertyType);
        //add the column to the DataTable
        dt.Columns.Add(dc);
    }

    //add the rows to the DataTable
    foreach (var item in list)
    {
        DataRow dr = dt.NewRow();
        //each property represents a column 
        //that has to be set
        foreach (var pi in properties)
        {
            dr[pi.Name] = pi.GetValue(item, null);
        }
        dt.Rows.Add(dr);
    }
    return dt;
}

That’s it. The returning DataTable will have three columns (int Id, string Name, string Position). If you want you can download the code for the class here. In this code for download I transformed the ToDataTable method into an extension method.

This article shows a tip on how you can do sorting using the LINQ OrderBy with Typed DataSets. For our example I’ll use the the Northwind Database and it’s Customers table. I’ll use two columns in this table. One is the ContactName which doesn’t have any null values and the Region column which has null values.

First lets populate the DataTable we want to query using TableAdapter I had previously generated in the DataSet.

CustomersTableAdapter ta = new CustomersTableAdapter();
DataSetNorthwind.CustomersDataTable dt = ta.GetData();

I want to use LINQ select all values ordering them by the Region column (which may be null). So here is the initial idea:

 var query = from r in dt.AsEnumerable()
                        orderby r.Region
                        select r;

Ok, no compilation errors but when you run the query you will get an error:

The value for column ‘Region’ in table ‘Customers’ is DBNull. -> System.InvalidCastException: ...

If you think about it this is expected. The nullable columns in the DataSet all have an IsNull method to check if the column is null or not and avoid this kind of error. We are going to use the IsRegionNull() method in our favor to help us solve the problem:

var query = from r in dt.AsEnumerable()
        orderby (r.IsRegionNull() ? "" : r.Region)
        select r;

I used the ternary operator to check if the column region is null or not. If it is then I’ll use an empty string as the value, if it’s not null I can call the Region property to get the value.

To see the ending result you can print all the values:

foreach (DataSetNorthwind.CustomersRow row in query)
{
     Console.WriteLine("{0} - {1}", row.ContactName, 
          row.IsRegionNull() ? "" : row.Region);
}

Of course you could have filtered out the rows with null regions, but the goal here is to show what you can do to order the rows when you have a column with values that may be null.

You know those tables in your systems that are almost static (never change) by are consulted thousands of times a day? Well, the other day at work some asked me if we could avoid all this trips to the database.

The solution I came up with creates a cache at our BLL (Business Logic Layer) avoiding DAL layer to be called. First of all our base architecture is based on the principles on the asp.net tutorial Creating a Business Logic Layer. This article basically creates something very similar to a proxy pattern before the DAL which gives me the perfect place to intercept any calls to the DAL and consult my cache.

To avoid the trip to the database, when the BLL is instantiated I do one query to the database which returns all the records in the table. The resulting DataTable is stored in my cache class which is declared as static in the class. This will make possible that the cache is populated only once. All subsequent queries will fetch the results from the Cache instead of making a trip to the database.

The cache table is very simple. It stores a DataTable and performs filters in it using LINQ syntax. I had other options but since I was starting to work with LINQ at the time it seemed like a good choice. For this code I use the Dynamic LINQ which I mentioned in my previous post.

In order to work with TypedDataSets (which was a requirement for me) I create the Cache class to accept the DataTable and DataRow types.

public class CacheDataTable<T, S>  where T : DataTable where S : DataRow
    {
        private T cacheDataTable;

        //stores the DataTable that will be used for future consults
        public CacheDataTable(T dataTable)
        {
            this.cacheDataTable = dataTable;
        }

        //returns a DataTable filtered by the expression
        public T GetData(string expression, params object[] values)
        {
            IEnumerable<S> tmp = ((IEnumerable<S>)cacheDataTable).AsQueryable().Where(expression, values);
            return getDataTablePopulated(tmp);
        }

        //returns all the records
        public T GetData()
        {
            return getDataTablePopulated((IEnumerable<S>)cacheDataTable);
        }

        //takes an enumeration and creates a new DataTable
        private T getDataTablePopulated(IEnumerable<S> rows)
        {
            T dt = Activator.CreateInstance<T>();
            foreach (S row in rows)
            {
                dt.ImportRow(row);
            }
            return dt;
        }
    }

After having the Cache class defined you can use it in the BLL like this:

public class CountryBLL
{
        CountryDAL dal;

        //the cache is static so that it is shared among all instances from the CountryBLL class
        private static CacheDataTable<DataSet1.CountryDataTable, DataSet1.CountryRow> cache;

        public TBG_CountryBLL()
        {
            dal = new CountryDAL();
            //tests if the cache class has not been created yet. Only happens at the first access
            if (cache == null)
            {
                cache = new CacheDataTable<DataSet1.CountryDataTable, DataSet1.CountryRow>(dal.GetData());
            }
        }

        public DataSet1.CountryDataTable GetData()
        {
            //gets all records from the cache
            return cache.GetData();
        }

        public DataSet1.CountryDataTable GetDataByPK(decimal id)
        {
            return cache.GetData("id == @0", id);
        }
}

Coded like this the CountryBLL will only cause a database connection at its first instantiation. This approach should only be used with tables which are static (or almost). If one record is inserted in this table it would demand the application to be restarted so that the static cache variable would be unloaded and created again. There are ways around this but it is not the objective of this post.

Hope this will help someone :-)

LINQ is great; I’m having all kinds of use to it lately. The other day I needed to do something different, I needed to write a LINQ query dynamically, constructing the LINQ query as a String and then executing it (which is pretty common thing when you’re using SQL).

I was already convinced that I would need to spend a couple of weeks writing such functionality. Turns out that Microsoft has already written a LINQ Dynamic Query Library. It doesn’t come with the framework but you can download it and include in your project. Take a look at ScottGu’s Blog to see his post about it.

Here is an example:

Suppose I have a LocalDBDataSet with a mapping to the Table Person (Name, Sex, Phone).

If I wanted to get all the people in the table and perform a LINQ query to filter only the Males (M) I could do it like this:

PersonTableAdapter ta = new PersonTableAdapter();
DataTable dt = ta.GetData();
IEnumerable<LocalDBDataSet.PersonRow> drCollection = 
    ((IEnumerable<LocalDBDataSet.PersonRow>)dt).AsQueryable().Where("SEX == @0", "M");

I know this isn’t the most exciting example ever and I would need dynamic LINQ to do it but I just wanted to show you what you could accomplish. You get the idea, right?

With this kind of resource you could accomplish a lot of cool stuff. In the next post I’ll show how you can write a Cache class that can be queried with LINQ to avoid unnecessary trips to the database.

You can download the LINQ Dynamic Query Library here or here