Efficient querying when using DTOs in Breeze

Question

We are using DTOs server side, and have configured a dbcontext using fluent api in order to give breeze the metadata it needs. We map 1:1 to our real database entities, and each DTO contains a simple subset of the real database entity properties.

This works great, but now I need to figure out a way to make queries efficient - i.e. if the Breeze client queries for a single item I don't want to have to create a whole set of DTO objects before I can filter. i.e. I want to figure out a way to execute the filter/sort on the actual entities, but still return DTO objects.

I guess I need to figure out a way to intercept the query execution in order to query my real database entities and return a DTO instead of the real database entity.

Any ideas for how to best approach this?

"I guess I need to figure out a way to intercept the query execution in order to query my real database entities and return a DTO instead of the real database entity." I don't understand what you are trying to do - it sounds like on your server you want to return a DTO instead of a full entity - have you tried using a select statement in your Breeze query? — PW Kad
I can do that (and do), but when I then expose the entities as an IQueryable<DtoType> property (using a linq statement in which I create the DTO in the select) a DTO is created for every entity before the IQueryable can filter on them. i.e. the problem here isn't creating and populating the DTO, it's being able to filter without having to create DTOs for every entity that doesn't match the filter. — fubaar

fubaar fubaar · Accepted Answer · 2014-02-28T23:37:09

Turns out that if you use projection in a link statement, e.g.

From PossibleCustomer As Customer
In Customers
Select New CustomerDto With {.Id = PossibleCustomer.Id,
                             .Name = PossibleCustomer.Name,
                             .Email = PossibleCustomer.Email}

.. then linq is smart enough to still optimize any queries to the database - i.e. if I query on the linq statement above to filter for a single item by Id, the database is hit with that query for just a single item and a single DTO is created. Pretty clever stuff. This only works if you do a direct projection in the linq statement - if you call off to a function to create your DTO then this won't work.

Just in case others are facing the same scenario, you might want to look at AutoMapper - it can create these projections for you using a model you create just once - avoids all those huge linq statements that are hard to read and validate. The automapper projections (assuming you stick to the simple stuff) still allow the linq to entities magic that ensures you don't have to do table scans when you create your DTOs.

Efficient querying when using DTOs in Breeze

1 Answers