Query DTO objects through WCF with linq to sql backend

Question

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.

Teoman Soygul · Accepted Answer · 2012-03-21 14:27:51Z

2

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.

answered Mar 21, 2012 at 14:27

Teoman Soygul

25.8k6 gold badges73 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter Hagström Over a year ago

It sounds like a good solution but I cant get it to work. I Suspect it has something to do with the fact that I use L2S and not EF

Peter Hagström Over a year ago

Found at least two problems. I can't use a helper method and since the context is wrapped in using it will be disposed when the collection is queried.

Teoman Soygul Over a year ago

Using L2S or EF doesn't matter. You can pool the data from any source. Since OData service objects are created 'per-request', having a using statement is the right way as the DataModel class instance will be created and destroyed along with the request.

Peter Hagström Over a year ago

I disagree on the using statement, the dispose method will be called as soon datacontext goes out of scope, which is before Get() gets called.

Nachiket Mehta · Accepted Answer · 2012-03-21 14:39:24Z

0

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?

answered Mar 21, 2012 at 14:39

Nachiket Mehta

3103 silver badges15 bronze badges

Comments

Simon_Weaver · Accepted Answer · 2013-04-28 11:07:39Z

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.

Collectives™ on Stack Overflow

Query DTO objects through WCF with linq to sql backend

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related