locked
Linq over ADO.NET Data Services from Silverlight - returning an integer rather than entire entity RRS feed

  • Question

  • Probably a basic question, sorry!

    I have this query that i run asynchronously from a silverlight client:
    DataServiceQuery<int> q =
                    (DataServiceQuery<int>) 
                        from p in db.User
                        where p.UserName == usernameEdit.Text && p.Password == passwordEdit.Text
                        select p.Id;

    Where I want to return the Id only of a particular user. But When I call BeginExecute I get the following exception:

    NotSupportedException was not handled by user code
    Can only specify query options (orderby, where, take, skip) after last projection

    I just checked in the debugger and the query is stating that it is unable to translate the linq expression to the URI, the expression is:
    {[10000].Where(p => ((p.UserName = value(DynamicsSilverlight.LoginPage).usernameEdit.Text) && (p.Password = value(DynamicsSilverlight.LoginPage).passwordEdit.Text))).Select(p => p.Id)}


    Am I restricted to only be able to return whole entities? Or is there some layer of technology that does not support this?

    Thanks.
    Saturday, September 6, 2008 7:19 AM

Answers

  • Providing filters on any part of the uri other then the last segment is not supported by the Astoria URI syntax and hence is not supported by the Linq to URI translator.  In this case, when you navigate to the Id property of User you are navigating from the User segment to the Id segement:

     

    http://myserver/mydataservice.svc/Users/Id

     

    So any filter provided would apply to Id not Users.  Hence the error.

     

    As you identified, the work around is to return the entire entity.  You can call AsEnumerable() and then do the projection, but that is a client side operation.

     

    In vNext we are looking at some ways to remove this restriction.

    Andy

     

    Saturday, September 6, 2008 3:30 PM
    Moderator
  • Hi

     

    Since you are trying to return a scaler value (userid) and it looks like you are not interested in updating the database you could also do the following...

     

    1 In your Astoria webservice define a [WebGet] method and do a Linq query using your ObjectContext

     

           

    Code Snippet
            [WebGet]
            public int GetUserId(string username, string pswd)
            {
                using (YourObjectContext ctx = new YourObjectContext())
                {
                    var q = from p in ctx.User
                            where p.UserName == username && p.Password == pswd
                            select p.First().Id;
                }
            }

     

     

    2 Configure your Service operation to allow access

    Code Snippet

            public static void InitializeService(IDataServiceConfiguration config)
            {
                // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
                // Examples:
                // config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);

                config.SetServiceOperationAccessRule("GetUserId", ServiceOperationRights.ReadSingle);
            }

     

     

    3 Call your webservice in your Silverlight application using this pattern

     

     

     

     

     

     

    Friday, December 5, 2008 7:46 PM

All replies

  • Providing filters on any part of the uri other then the last segment is not supported by the Astoria URI syntax and hence is not supported by the Linq to URI translator.  In this case, when you navigate to the Id property of User you are navigating from the User segment to the Id segement:

     

    http://myserver/mydataservice.svc/Users/Id

     

    So any filter provided would apply to Id not Users.  Hence the error.

     

    As you identified, the work around is to return the entire entity.  You can call AsEnumerable() and then do the projection, but that is a client side operation.

     

    In vNext we are looking at some ways to remove this restriction.

    Andy

     

    Saturday, September 6, 2008 3:30 PM
    Moderator
  • Thanks andrew, I did some more reading after the post and came to that conclusion. My main concern was performance and trying to minimise the amount of data being exchanged, do you think this is an issue? 

    Any ways around it? Can I write some custom service operations or use a seperate web service for operations that don't require full entities to be returned?

    Are there any articles on performance with ado.net data services?

    Thanks.
    Saturday, September 6, 2008 3:51 PM
  • If you are paying for bandwidth, have big entities with lots of fields, and/ or your client app will be making a significant amount of queries then it might be a consideration.

     

    I think the biggest thing to keep in mind is that when you bring down the entire entity it will be tracked by your data context, even if you just project a field and never see the entity,  Depending on your merge options, this may have some interesting consequences.

     

    Sunday, September 7, 2008 2:37 PM
    Moderator
  • Hi

     

    Since you are trying to return a scaler value (userid) and it looks like you are not interested in updating the database you could also do the following...

     

    1 In your Astoria webservice define a [WebGet] method and do a Linq query using your ObjectContext

     

           

    Code Snippet
            [WebGet]
            public int GetUserId(string username, string pswd)
            {
                using (YourObjectContext ctx = new YourObjectContext())
                {
                    var q = from p in ctx.User
                            where p.UserName == username && p.Password == pswd
                            select p.First().Id;
                }
            }

     

     

    2 Configure your Service operation to allow access

    Code Snippet

            public static void InitializeService(IDataServiceConfiguration config)
            {
                // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
                // Examples:
                // config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);

                config.SetServiceOperationAccessRule("GetUserId", ServiceOperationRights.ReadSingle);
            }

     

     

    3 Call your webservice in your Silverlight application using this pattern

     

     

     

     

     

     

    Friday, December 5, 2008 7:46 PM
  • I have the same question too, projecting a sub set of fileds instead of the whole set of the entity objects, and is looking for the answer in this forum.  This should be the basic feature, and I am surpise to learn that Ado.net data service doesn't support it (just like "count").


    I think the work around is either to create a new trim down set of Entity Model or another trim down set of entity objects in the same model .

    Does anyone else has a better solution?

    thanks
    Saturday, May 16, 2009 3:18 PM
  • Ditto Here. I can't believe you can't do this. Is this possible with v1.5? If not, it sure should be!  There are a million cases where you just want to list something, and there is absolutely no reason to send all of the data. If I do a report, I can bring down a small dataset or I could bring down all of the objects. If I did all of the objects, this could be a huge amount of information, whereas a small subset could be a few K. (i.e. never use Select * from in sql, when you can pick the fields you really want!)

    Please tell me that this functionality is coming soon, without me having to create custom method stubs for everything.
    Monday, May 18, 2009 5:08 PM
  • Hi John / MC.Sean ,

    If you want to return a list of primitives ( ex : int , string , date) then using a service operation which does the projection on the server-side should be the best way to go about this.
    I agree that there is absolutely no reason to bring all of the data down to the client . I suggest that you design your system in such a  way that only the data required by the client  is sent down via the service is in the model used by the data service.

    "If I do a report, I can bring down a small dataset or I could bring down all of the objects. If I did all of the objects, this could be a huge amount of information, whereas a small subset could be a few K. (i.e. never use Select * from in sql, when you can pick the fields you really want!)"

    I think this is an ideal scenario for specifying a separate data service with a separate model which is more tuned towards the expected entities /types in a report .
    Think of conceptual models as views in T-SQL which allow you to only see those fields that are required by the client .
    You can have a separate model that drives the CRUD operations on the data and another one for reporting both of which go against the same backend.
    If you want to discuss this further , please drop an email to me at : PHANIRAJ AT Microsoft DOT COM


    MC.Sean ,
    Count is suported in the V1.5 CTP1 , please download it from here : ADO.NET Data Services v1.5 CTP1 
    I look forward to your feedback on this feature .

    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Monday, May 18, 2009 10:12 PM
    Moderator
  • The whole point to ADO.NET Data services is to provide a generic way of accessing data. I could just as easily return Entities over the wire using WCF without Data services by hard coding all of my requests, and I can get lists just like you suggest the same way.

    ADO.NET Data Services should be a protocol to abstract the data access layer across the wire from direct access to webservices based access. In a perfect world, it would share a base object for the context with Entties and as a result allow for a generic context to be created that would allow you to pick which system you wanted to use in code, for both local and remote access with the benefits of each.

    But in any case, it should allow for abstract querying with security applied by user on the server level, just as if I was doing the entity query locally.

    That it doesn't is very disappointing because it now means that every time I create a report, I have to update my server service code as well and schedule rollouts of that along with reports which severely limits updates because it will affect EVERYONE instead of just the person requesting the report.

    This should be a required feature add for Data Services 2.0 in .NET 4.0.
    Tuesday, May 19, 2009 12:28 PM
  • I've come up with a temporary solution to projection, I put it on my blog:

    http://antscode.blogspot.com/2009/07/select-filter-for-adonet-data-services.html

    It's not very elegant, but seems to work well for me.

    Anthony.
    Friday, July 24, 2009 12:42 AM