locked
Can I return just a specified list of fields in the sql generated by Linq for DocumentDb? RRS feed

  • Question

  • i have the following linq query. I am building up the Where clause in the applyfilters method, so nothing special there. Linq for Documentdb seems to always generate a sql statement starting with "select *". in my scenerio i dont what to return all the fields in my search page i wrote because some of them are kind long and dont need to be returned unless someone clicks on an specific item returned to get the details on that item. Is there any way to setup a Linq For DocumentDb query to only return a specified list of fields instead of "select *"?

    IQueryable<TestRunModelItem> azureQuery = _documentClient.CreateDocumentQuery<TestRunModelItem>(
                    UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), queryOptions)
                    .Take(criteria.PageSize)
                    .OrderByDescending(x => x.Time.Epoch);
                azureQuery = ApplyFilters(azureQuery, criteria.Filters);


    • Edited by Kirk Q Tuesday, June 28, 2016 9:07 PM
    Tuesday, June 28, 2016 9:07 PM

Answers

  • Hi Kirk,

    You can use the following CreateDocumentQuery(dynamic) overload:

    // SQL querying allows dynamic property access
    var query = new SqlQuerySpec(
        "SELECT b.title FROM books b WHERE b.title = @title", 
        new SqlParameterCollection(new SqlParameter[] { new SqlParameter { Name = "@title", Value = "War and Peace" }}));
    
    dynamic document = client.CreateDocumentQuery<dynamic>(collectionLink, query).AsEnumerable().FirstOrDefault();

    Hope this helps!


    Best Regards,
    Carlos Sardo

    • Marked as answer by Kirk Q Wednesday, June 29, 2016 2:02 PM
    Wednesday, June 29, 2016 8:50 AM

All replies

  • Hi Kirk,

    You can use the following CreateDocumentQuery(dynamic) overload:

    // SQL querying allows dynamic property access
    var query = new SqlQuerySpec(
        "SELECT b.title FROM books b WHERE b.title = @title", 
        new SqlParameterCollection(new SqlParameter[] { new SqlParameter { Name = "@title", Value = "War and Peace" }}));
    
    dynamic document = client.CreateDocumentQuery<dynamic>(collectionLink, query).AsEnumerable().FirstOrDefault();

    Hope this helps!


    Best Regards,
    Carlos Sardo

    • Marked as answer by Kirk Q Wednesday, June 29, 2016 2:02 PM
    Wednesday, June 29, 2016 8:50 AM
  • hey thanks for the suggestion. i should have specified that i was trying to avoid creating a sql string. Linq just seems like the "better" way to build up a dynamic query like i have where the where clause changes.

    is there any way to accomplish this with linq instead of a sql string?

    if this is the only way, then i may change my code to use a sql string to get the faster response time.

    Wednesday, June 29, 2016 12:56 PM
  • Hi Kirk,

    I don't think so, unless I missed it in the documentation. Personally, I use dynamic when I wish to retrieve anything different from my <T> entity. Which is also your case, right?

    Hope this helps!


    Best Regards,
    Carlos Sardo

    Wednesday, June 29, 2016 1:25 PM
  • yes, even though my <T> object contains some of these bigger fields, for purpose of basic querying, i dont want to return them because the overhead of great. i only want to get all the fields when someone wants to look at the details for a single item.
    Wednesday, June 29, 2016 2:03 PM