Query DTO objects through WCF with linq to sql bac

问题:

I am working on a project where we need to create complex queries against a WCF service.

The service uses linq to sql at the backend and projects queries to data transfer objects like this:

    dbContext.GetQueryable()
                  .Where(x => x.Id == formatId)
                    .Select(x => FormatHelper.PopulateMSFormat(x))
                    .ToList();

What I want to do is to specify a query on the client side, lets say i want to query all formats having a certain property or a couple of them. Something in the style of this:

     var assets = client.QueryForAssets().Where(x => (x.name == "Test" || x == "Arne") && x.doe ==  "john");

Iam aware that I can't return IQueryable over WCF but that something like that can be done with OData services. The problem is that I have to return the DTO's and OData let me quite easily bind to L2S-datacontext which exposes my data model and not the DTO's.

So is there a good way of serializing a query against the DTO that efficiently will propagate to the l2s layer?

I thought about writing my own query language, but I found out that it would be quite hard to build the correct expression tree as a predicate to l2s as there is no mapping from the DTO to the linq classes.

回答1:

With OData services, you are not bound to return database entities directly. You can simply return any DTO in queryable format. Then with the help of LINQ's Select() method, you can simply convert any database entity into DTO just before serving the query:

public class DataModel
{
  public DataModel()
  {
    using (var dbContext = new DatabaseContext())
    {
      Employees = from e in dbContext.Employee
                  select new EmployeeDto
                  {
                    ID = e.EmployeeID,
                    DepartmentID = e.DepartmentID,
                    AddressID = e.AddressID,
                    FirstName = e.FirstName,
                    LastName = e.LastName,
                    StreetNumber = e.Address.StreetNumber,
                    StreetName = e.Address.StreetName
                  };
    }
  }

  /// <summary>Returns the list of employees.</summary>
  public IQueryable<EmployeeDto> Employees { get; private set; }
}

You can now easily set this up as a OData service like this:

public class EmployeeDataService : DataService<DataModel>

For full implementation details, see this excellent article on the subject. OData services are actually very very powerful once you get the hand of them.

回答2:

I believe that you can return DTO's by using OData services.

Take a look at http://www.codeproject.com/Articles/135490/Advanced-using-OData-in-NET-WCF-Data-Services. Particularly the 'Exposing a transformation of your database' section. You can flatten your entity objects into a DTO and have the client run queries against this DTO model.

Is that something you are looking for?

回答3:

If you have long complicated entities then creating a projection by hand is a nightmare. Automapper doesn't work because LINQ cannot use it in conjunction with IQueryable.

This here is the perfect solution : Stop using AutoMapper in your Data Access Code

It will generate a projection 'magically' for you and enable you to run an oData query based on your DTO (data transfer object) class.

    [Queryable]
    public IQueryable<DatabaseProductDTO> GetDatabaseProductDTO(ODataQueryOptions<DatabaseProductDTO> options)
    {
        // _db.DatabaseProducts is an EF table 
        // DatabaseProductDTO is my DTO object
        var projectedDTOs = _db.DatabaseProducts.Project().To<DatabaseProductDTO>();

        var settings = new ODataQuerySettings();
        var results = (IQueryable<DatabaseProductDTO>) options.ApplyTo(projectedDTOs, settings);

        return results.ToArray().AsQueryable();
    }

I run this with

/odata/DatabaseProductDTO?$filter=FreeShipping eq true

Note: this article is from a couple years ago and it's possible that by now AutoMapper has functionality like this built in. I just don't have time I check this myself right now. The inspiration for the above referenced article was based on this article by the author of AutoMapper itself - so it's possible some improved version of it is now included. The general concept seems great and this version is working well for me.