How to architect my service/repository code with Linq2Sql!

Question

Hi,

I have a problem in architecting my application.

I have the following structure with only important aspects shown.

namespace Domain
{
   public class Invoice
   {
     //properties
   }

public class InvoiceRepository
{
   public Linq2SqlContext context = new Linq2SqlContext();

   public IQueryable<Invoice> GetInvoices()
   {
              var query = from inv in _dbctx.Invoices orderby inv.invInvoiceDate descending select GetInvoice(inv) ;

            return query;

   }
}
public class InvoiceService()
{
   public InvoiceRepository _repository = new InvoiceRepositroy();
   public IQueryable<Invoice> GetInvoices()
   {
      return _repository.GetInvoices();
   }   

}
}

namespace MyApp
{
   public class UI
   {
      public InvoiceService _service =  new InvoiceService();
      public void FilterInvoices()
      {
            var query =
                   (
                     from i in _service.GetInvoices()
                     from s in _service.GetStatuses()
                     where i.ProjectID == _projectid &&
                           s.ID == i.Status
                     select new
                     {
                         InvoiceID = i.ID,
                         DocumentTotal = i.TotalDue.ToString(),
                         Created = i.Created,
                         WeekEnding = i.WeekEnding,
                         Status = s.staStatus
                     }
                   ).Skip(_pageIndex * _pageSize).Take(_pageSize);

      }

   }
{

So I want to return IQueryable from my service so I can filter from client code. But the problem I'm coming up with is the FilterInvoices method errors with "No supported translation to sql" because of the GetInvoice method which is iused to return an Invoice entity (this is a layer on top op the LInq2 sql layer) and not an Linq2sql Invoice entity.

So how do I return a IQueryable from my service with this structure?? Also how do I sort and return a IQureyable in repository GetInvoices.

Hope this makes sense.

Malcolm

Answer 1

This is just off the top of my head, but you could try:

from i in _service.GetInvoices().AsEnumerable()

Answer 2

linq2sql thinks GetInvoice (within GetInvoices) is a stored procedure. One way around it

var query = from inv in _dbctx.Invoices orderby inv.invInvoiceDate descending select inv ;

though that would pass back the objects generated by your datacontext. If you wanted to populated custom objects you could iterated over the collection creating your custom Invoice objects and populating them.

foreach(var inv in query) { somelist.Add(new MyCustomInvoince() { id = inv.id ... }

EDIT: The above will return a list. Use the following to return IQueryable

return from item in query
    select GetInvoice(item);

The difference is at this stage your are using Linq2Objects, and that provider will know how to call GetInvoice

Answer 3

You cannot query with LTS (Linq to SQL) something built 'on top' of the LTS layer. The reason is that the LTS entities layer is a mapping of the content of you database, and the query you perform is 'just' translated into SQL.

I personnaly use another approach to keep an independance between my layers...

I create interfaces that match my LTS entites, and I use the Cast<>() method to have my repository return the interface instead of the concrete implementation.

It works perfectly.

You need to extend the base entity (no pb as it is a partial class) :

partial class Employee : IEmployee

And you need this property in your repository :

public IQueryable<IEmployee> Query
{
    get
    {
        return this._context.Employees.Cast<IEmployee>();

    }
}

Based on this, you could code a generic repository, but it's another story (more complicated)