Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You have some success by passing your filter criteria as simple parameters using the Breeze <code>.withParameters({...})</code> clause. You can still use <code>orderBy</code>, <code>take</code>, <code>skip</code>.</p> <h2>Breeze, DTOs and the Web API</h2> <p>Do you need to use WCF OData? Could you switch to Web API where you have more flexibility?</p> <p>Suppose we reimagine your example in the Northwind world of DocCode. For convenience, you define a DTO class</p> <pre> public class ProductDto { public int ProductID { get; set; } public string ProductName { get; set; } } </pre> <p>This is not needed, strictly speaking. But you created this class so you don't ever have to see the ugly anonymous-type-name that is generated for your projection.</p> <p>Then you add a query method to the <code>NorthwindController</code> like this one:</p> <pre> [HttpGet] public IQueryable ProductDtos() { return _repository.Products // TODO: move the following into the repository where it belongs .Where(x => x.CategoryID == 1) // a surrogate for your 'Owner' filter .Select(x => new ProductDto { ProductID = x.ProductID, ProductName = x.ProductName }); } </pre> <p>When your client issues a breeze query such as</p> <pre> var q = breeze.EntityQuery.from('ProductDtos') .where('ProductName', 'startsWith', 'C') .orderBy('ProductName') .take(5); // execute it </pre> <p>it resolves to the following URL</p> <pre> http://localhost:47595/breeze/Northwind/ProductDtos?$filter=startswith(ProductName,'C') eq true&$orderby=ProductName&$top=5 </pre> <p>and returns four {ProductID, ProductName} objects.</p> <p>If you describe <code>ProductDto</code> in client-side metadata, breeze will treat these objects as entities and cache them. You'll get change notification, change tracking, validation, etc. You can save the changes back to the server where, in the <code>beforeSaveEntities</code> method, you can validate them and convert them back into <code>Product</code> entities so that EF can save them to the database. I'm not going into details in this answer but I want you to know that you can do it.</p> <h3>Restricted filtering</h3> <p>Note that you <strong>can only filter by the projected properties</strong>, not by properties in the undisclosed root type. For example, the following query fails because <code>Product.SupplierID</code> is not among the selected <code>ProductDto</code> properties:</p> <pre> http://localhost:47595/breeze/Northwind/ProductDtos?$filter=SupplierID eq 18&$orderby=ProductName&$top=5 </pre> <p>The response is</p> <blockquote> Message: "The query specified in the URI is not valid.",<br/> ExceptionMessage: "Type 'Northwind.Models.ProductDto' does not have a property 'SupplierID'.", </blockquote> <p>Given your security concerns about undisclosed properties, I assume <strong>you would want this query to fail</strong>.</p> <p>But if you actually need to filter by some criteria that are not in the projected type, you could change the server method to accept a parameter. For example:</p> <pre> [HttpGet] public IQueryable ProductDtos(int? supplierID=null) { // TODO: move the following into the repository where it belongs var query = _repository.Products .Where(x => x.CategoryID == 1); // a surrogate for a security filter if (supplierID != null) { query = query.Where(x => x.SupplierID == supplierID); } return query.Select(x => new ProductDto { ProductID = x.ProductID, ProductName = x.ProductName }); } </pre> <p>Now the client can write:</p> <pre> var q = breeze.EntityQuery.from('ProductDtos') .withParameters( {supplierID: 18} ) // &lt;-- passed as arg to the GET method .where('ProductName', 'startsWith', 'C') .orderBy('ProductName') .take(5); // execute it </pre> <p>which resolves to</p> <pre> http://localhost:47595/breeze/Northwind/ProductDtos?$filter=startswith(ProductName,'C') eq true&$orderby=ProductName&$top=5&supplierID=18 </pre> <p>and the result is two <code>ProductDto</code> objects that pass all filter criteria.</p> <p>p.s.: I'm not making this up. I tried this code and got these results exactly as described in this answer.</p>
    singulars
    1. This table or related slice is empty.
    1. POBreeze $filter projections
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USWard
    1. USWard
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWow! Ward thanks a million! I've been struggling to parse and the URI on the server so I could shift the $filter to before the $select on the fly but your solution seems much more elegant. Breeze is an amazing technology and I'm still wrapping my head around all its possibilities, thank you for your suggestions and all your hard work. I am going to follow your examples above and redesign accordingly, I’ll let you know how I make out. Regards, Mike Pisano
      singulars
      1. PO
      1. USMike
    2. CORegarding Need for WCF Odata, it’s just where I ended up after multiple test applications trying to decide between WCF\ODATA\RIA while I was looking at Lightswitch HTML, MVC and other technologies prior to going the SPA route. This is a brand new application and I have no prerequisites except EF.
      singulars
      1. PO
      1. USMike
    3. COWard, I have it working but now I’m struggling because seems WebApi’s JSON shape doesn’t permit $inlinecount=allpages responce even though I’ve added IncludeTotalCount() to the query. Seems like I have to implement PageSize attributes from what I’m reading, any insight?
      singulars
      1. PO
      1. USMike
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload