locked
Generic Repository Pattern using LINQ to SQL? RRS feed

  • Question

  • I am trying to create a generic repository that mediates domain model and the data access layer using LINQ to SQL. As follows

     

    public interface IRepository<T>

    {

        T GetByCode(int code);

        IList<T> GetAll();

    }

     

    public class Repository<T> : IRepository<T> where T : class

    {

        private NorthwindDataContext db = new NorthwindDataContext(ConfigurationManager.ConnectionStrings[1].ConnectionString);

     

        #region IRepository<T> Members

     

        public T GetByCode(int code)

        {

            // The below returns a Customer matching the code.

            // HOW COULD THIS BE GENERISED.

            //var result = (from c in db.Customers

            //              where c.CustomerID == code

            //              select c).Single();

     

            //return result;

        }

     

        public IList<T> GetAll()

        {

            return db.GetTable<T>().ToList();

        }

     

        #endregion

    }

     

    The GetAll method returns a collection of passed type. I am unable to figure out the generic LINQ query in the GetByCode method that would return the passed type by code. Each entity in my domain defines Code property that maps to code field in the database.

     

    Please help. Thanks

    Monday, August 11, 2008 6:48 AM

Answers

All replies

  • There's no easy way to generalize this (other than fiddling with expression trees or using the dynamic LINQ sample). A complicating factor is that you're naming the primary key property differently for each table. If it was always called ID, you could define an interface as follows:

    public interface IDProvider { int ID{ get; } }

    and then write the following extension method:

    public static class Extensions
    {
        public static T ByID<T> (this IQueryable<T> source, int id) where T : IDProvider
        {
            return source.Single (x => x.ID == id);
        }
    }

    But then you'd still run into trouble with non-integer PKs, and PKs that used multiple columns.

    And you're not saving that much code anyway. How bad is it just to do this?

    Customer c = db.Customers.Single (c => c.ID == id);

    ?

    Joe

    Monday, August 11, 2008 11:43 AM
  • If consumers of the IRepository can tell you the type they want, they can tell you which property on that type cooresponds to the ID.

     

     

    Try either of these:

     


    public interface IRepository<T>
    {
        T GetByCode(Expression<Func<T, int>> codeProperty, int code);
        IList<T> GetAll();
    }

    or

    public interface IRepository<T>
    {
        T GetByFilter(Expression<Func<T, bool>> filter);
        IList<T> GetAll();
    }

    Monday, August 11, 2008 2:07 PM
  • Hi David

     

    Could you please give an example for the implementation of the

     

    T GetByCode(Expression<Func<T, int>> codeProperty, int code);
    T GetByFilter(Expression<Func<T, bool>> filter);

    public T GetByCode(Expression<Func<T, int>> codeProperty, int code)

    {

    var result = from ... ???

    }

     

    public T GetByFilter(Expression<Func<T, bool>> filter)

    {

    var result = ...result ???

    }

     

    Sorry, trying to gain some knowledge here. I am nee to LINQ and trying to get hold of some fundamentals. There is stack of information on the web, but could you please point some good starting resources to learn LINQ fundamentals. I am hearing about LINQ to SQL, LINQ to ADO.NET, LINQ to Entity, LINQ to XML, extension to IEnumerable<T>,  IQueryable, Expressions etc. but what is the rationale behind all this?

    Tuesday, August 12, 2008 11:31 PM
  • The first one is tricky, because you have to do some work to blend the Expression<Func<T, int>> with the int into a new Expression<Func<T, bool>>.

     

     

     

    The second one is cake:

     

    public T GetByFilter(Expression<Func<T, bool>> filter)

    {

       return db.GetTable<T>().Where(filter).FirstOrDefault();

    }

     

    Called with:

     

      IRepository<Customer> myDB = Repository<Customer>();

      Customer theCustomer = myDB.GetByFilter(c => c.CustomerID == 1)

    Wednesday, August 13, 2008 12:28 AM
  • >could you please point some good starting resources to learn LINQ fundamentals.

     

    You can't beat the .net framework documentation.  These are the types that are used and the documentation for them.

     

    Start with LINQ to objects, the mighty System.Linq.Enumerable static class and the 101 LINQ samples that cover basic query composition.

    http://msdn.microsoft.com/en-us/library/system.linq.enumerable_methods.aspx

    http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

     

    If that stuff didn't make sense, you need to go back to basics with the c# 3.0 language enhancement.  Make sure to understand extension methods (how implementation is added to IEnumerable<T>) and anonymous methods (how to make a method with no name).

    http://msdn.microsoft.com/en-us/library/bb308966.aspx

     

     

    Moving on, LINQ to SQL, with the System.Linq.Queryable static class (sister to the Enumerable) and the System.Data.Linq.DataContext

    http://msdn.microsoft.com/en-us/library/system.linq.queryable.aspx

    http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.aspx

     

    and LINQ to XML, which is really just an alternate (and improved) implementation of System.Xml.  Mainly it is more aware of IEnumerable<T>.

    http://msdn.microsoft.com/en-us/library/system.xml.linq.xelement.aspx

     

     

    When all else fails, never forget that queries are not executed until they are enumerated (by foreach, or ToList(), or Count(), etc)

    Wednesday, August 13, 2008 12:43 AM
  • Thanks for the references. It could take me w while to score thorugh those.

    Could you please try some light on what have you done here?

     

    Expression<Func<T, int>> codeProperty, int code
    Expression<Func<T, bool>> filter

    They sounds alien to me.

     

    And why would it be tricky to implement
    T GetByCode(Expression<Func<T, int>> codeProperty, int code);


    A brief description from the expert would be quicker than browsing through loads of pages.

    Thanks

     

    Wednesday, August 13, 2008 12:46 PM
  • Quicker for whom?

     

    Here's my thought process:

     

    The core task is to filter a collection.

    Since the collection is really a database mapping implementation, I would probably use the System.Linq.Queryable.Where extension method.

    http://msdn.microsoft.com/en-us/library/bb535040.aspx

     

    This method expects a Expression<Func<T, bool>> but what the heck is that.

    http://msdn.microsoft.com/en-us/library/bb335710.aspx

     

    "The expression tree is an in-memory data representation of the lambda expression. [...] You can interact with the data in the expression tree just as you can with any other data structure.

    The ability to treat expressions as data structures enables APIs to receive user code in a format that can be inspected, transformed, and processed in a custom manner. For example, the LINQ to SQL data access implementation uses this facility to translate expression trees to Transact-SQL statements that can be evaluated by the database. "

     

    So, when I assign a instance of Func<T, bool> to a reference of Expression<Func<T, bool>>, a tree is generated.  If I am very lucky/careful/simple, that tree could be translated to correct T-SQL.

     

    Also from that article:

    Code Snippet

    // Lambda expression as executable code.
    Func<int, bool> deleg = i => i < 5;

    // Lambda expression as data in the form of an expression tree.
    System.Linq.Expressions.Expression<Func<int, bool>> expr = i => i < 5;

     

     

    The first code generates an anonymous method.  The second code generates an expression tree that might later be compiled to an anonymous method or translated to T-SQL.

     

     

    If I have a Expression<Func<T, int>> and some int, I should be able to combine them in some way to produce a Expression<Func<T, bool>>  (remember, the article said that trees could be inspected and transformed).  This is not a trivial thing to do, here's one way: http://msdn.microsoft.com/en-us/library/bb546136.aspx

     

     

     

     

     

    Wednesday, August 13, 2008 8:54 PM
  • Since I was looking for something similar and this is one of the first results in Google search I thought I would add a comment that the Suteki Shop ecommerce example has a pretty good implementation of this. Can be found here: http://www.asp.net/mvc/learn/

    You will need to include some extra dependencies like the GetPrimaryKey() extension method.

    Hope this helps...

    A quick snippet from the source:

    public virtual T GetById(int id)
    {
        var itemParameter = Expression.Parameter(typeof(T), "item");
    
        var whereExpression = Expression.Lambda<Func<T, bool>>
            (
            Expression.Equal(
                Expression.Property(
                    itemParameter,
                    typeof(T).GetPrimaryKey().Name
                    ),
                Expression.Constant(id)
                ),
            new[] { itemParameter }
            );
    
        return GetAll().Where(whereExpression).Single();
    }
    Friday, May 22, 2009 12:19 AM
  • hi denny

    thanks for the comments. could u please provide implementation of GetPrimaryKey() extension method. thanks
    -= JL =-
    Wednesday, September 2, 2009 7:54 AM