Linq, repository pattern and database abstraction problem

Question

Helo,

i have spent some time now to solve a problem for which i have no solution till now. I have a predefined very huge database, whose structure is fixed. I use the repository pattern to create a abstraction layer between service code and database logic. The problem is, that i need to apply some processing to the database object before passing them out of the repository. Thus i can't use the Linq Entities directly.

Basically a repository method looks like this:

public IList<Bookingcode> FindBookingcode(int id) {
   return (from b in _db.BOOKINGCODE
           where b.ID == id
           select new Bookingcode {
              Id = b.ID,
              Name = b.NAME.Trim()
           }).ToList();
}

This works good so far. But i have a lot of objects which should be individually composed. FindBookingcode() should return a nearly complete object with other objects like catalogs and so on. My Problem now is that i have to rewrite the mapping a lot of times like in this example:

public IList<Bookingcode> FindBookingcode(int id) {
    return (from b in _db.BOOKINGCODE
            join c1 in _db.CATALOG on b.CATALOGID equals c1.ID
            where b.ID == id
            let refs = (
                from bc1 in _db.BOOKINGCODE
                join p in _db.PACKAGE on bc1.ID equals p.BOOKINGCODE
                join bc2 in _db.BOOKINGCODE on p.PACKAGEREF equals bc2.ID
                join c in _db.CATALOG on bc.CATALOGID on bc2.CATALOGID equals c.ID
                where bc1.ID == b.ID
                select new PackageInfo {
                   ID = p.ID
                   BookingcodeRef = new Bookingcode { 
                       ID = bc2.ID,
                       Catalog = new Catalog { ID = c.ID }
                   }
                })
            select new Bookingcode {
              ID = b.ID,
              PackageInfo = refs.ToList()
            }).ToList();

}

I also have some L2O processing within the repository which assembles the returned objects. Another thing i have no cool solution for, is a way to tell the repository what it should fetch, like FindBookingcode(id, includePackageInfo, includeCatalog).

So here are the questions:

1) Is this approach totally stupid?

2) Can you guide me to a solution which makes the remapping simpler?

3) How to implement the DDD's criteria mechanism

thanks

Answer 1

Are you using Linq to SQL or Entity Framework?

I've assumed here that you're using Entity Framework as you referred to "LINQ Entity" in your question.

You should be able to do this sort of mapping using the Entity Framework navigation properties. You'll need to add navigation properties to each entity that you'd like to map in this way. It's as easy as telling the visual designer which attribute maps to a corresponding attribute in another entity.

Take a look at the How do I Entity Framework video series for a very quick starter guide. I'd recommend watching the whole series as they are very informative.

Answer 2

In my repositories I have a separate build method that takes in a LINQ to SQL entity and returns a business object.

The build method looks something like (this.Container is a Unity IoC container and not important for the example):

private IGroup BuildGroup(Entities.Group group)
{
    IGroup result = this.Container.Resolve<IGroup>();
    result.ID = group.GroupID;
    result.Name = group.Name;

    return result;
}

Then each method uses the build method to return a business object:

public override IGroup GetByID(int id)
{
    try
    {
        return (from g in this.Context.Groups
                where g.GroupID == id && g.ActiveFlag
                select this.BuildGroup(g)).Single();
    }
    catch (InvalidOperationException)
    {
        return null;
    }
}

This works by getting back each LINQ to SQL entity from the database and running it through the build method so your result in this case would be an enumerable of your business objects instead of LINQ to SQL entities.

Answer 3

If I call a repository method like

var list = x.FindBookingcode(int id)

I would expect to be able to add items to the list and not worry about how it is going to get persisted to the database. In the sample implementation there is no way for your repository function to detect that someone added something to the list so it cannot do anything about it.

I would also expect to be able to pass specifications. Specifications are basically delegates that acts on your repository type returning a boolean to indicate whether it must be considered for the query or not. Usually you would implement a base specification that implements logical and, or and not operators so that you could easily combine specifications into new ones.

Answer 4

First of all - sry for the long time for my answer^^ I was just so busy that i have forgotten to answer.

John, your solution is quite nice to solve my multi remapping problem.

DoctaJonez, i am using Linq2SQL. I have done some tests with Entity Framework but also with NHibernate. The root of the mapping problems is, that i have a given database schema which is sometimes realy ugly. So i have a lot processing to do when accessing the data. NHibernate works quite well and i think i will add it to the project in the near future.

Hans, your point is quite right. But because the project is just a query api for a database there are no writes to the database. It's more an dll with services which is used by other applications to get infomations out of a booking software which is used in my company.

You have mentioned "Specifications" which is also a good point. Because i have huge search queries with a lot of filters, i really need a good api for the repository. The problem with this is, that for example, different combinations of the filters also start different processing logic. This also should create different SQL to be effective.

Because i have mapped objects and using Linq to SQL i can't get the specifications work like they should. So i have a lot of methods on the repository to fetch objects whith different crierias, like so:

Bookingcode FindBookingcode(int id);
Bookingcode FindBookingcode(string code, string catalog);
List<Bookingcode> FindBookingcode();
List<Bookingcode> FindBookingcode(int[] ids);
List<Bookingcode> FindBookingcode(string code);
List<Bookingcode> FindBookingcode(string[] codes);
List<Bookingcode> FindBookingcodes(Catalog catalog);

Because im not using IQueryable i can't chain things like

repo.GetBookingcodes().WithCatalog("WI09").WithCode("XYZ10001");

For larger interfaces like Searches i use filter objects like so

srv.SearchTransactions(new TransactionSearchFilter { 
    Customer = 1234, Destination = "DXB", ExtractServices = true });

The repository then has some logic to get the stuff out of the database. Within the repository im testing to get something to work like this

    repo.QueryTransactions()
    .Include<Customer>()
    .Include<TransactionsService>()
    .FilterBy(TransactionSearchFilter.Customer, 1234)
    .FilterBy(TransactionSearchFilter.Destination, "DXB")
    .Execute();

which internally builds a critery/specification. But this really looks like Entity Framework, and perhaps if i am deeper in there i can use it or NHibernate.

The most problematic sections in my repository are the searches because in need create some sort of dynamic SQL in terms of performance. I have played around with PredicateBuilder but couldn't solve some queries where i change/include table joins if specific filters are set.

I think i have to 'scan' more documentation. Thanks for your replies.