locked
Contains Not Working in LightSwitch LINQ Query RRS feed

  • Question

  • All, I'm trying to do something that seems so simple in SQL within an entity class in LightSwitch and i'm using "Contains".  Here are my tables:

    TruckSeries:  TruckSeries_Id, TruckSeries

    Claims: Claim_Id, ClaimNo, Truck_Id (FK)

    Trucks:  Truck_Id, TruckNo

    Projects: Project_Id, ProjectNo

    ProjectsTruckSeries:  Project_Id (FK), TruckSeries_Id (FK)

    When I try the following in code within LightSwitch, I get an error saying that "Contains is not support".  Any ideas?

    var truckSeriesIDs = (from tsi in DataWorkspace.MyDataSource.ProjectsTruckSeries
    select tsi.TruckSeries_Id);

    var query = (from c in DataWorkspace.MyDataSource.Claims
    where truckSeriesIDs.Contains(c.Trucks.Truck_Id)
    select c);

    Why does "Contains" not work with the IDataServiceQueryable?

    Thanks to anyone that can help me out on this.

    Monday, July 2, 2012 7:05 PM

Answers

  • You're welcome. I'd like to help you get this solved, if I can.

    Try something along the lines of:

    var query = this.DataWorkspace.MyDataSource.Claims
        .where(x => x.truck.ID = "something")
        .OrderBy(x => orderby x.ComplaintDate)
        .select wh);
    var exists = query.Any();

    I have to admit, that I'm not quite "getting" what you're trying to do. This is because, as you say, you're new at C#/LINQ, combined with the fact that my head gets easily muddled these days unfortunately, especially when I don't "get" what's trying to be explained.

    I'm not sure if what I've given you above even makes sense. It late here & I'm tired, as well as muddled, lol.

    If that code doesn't help you find your answer, I'll try to give it a better crack tomorrow (for me down under in the land of OZ).


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    • Marked as answer by Otomii Lu Thursday, July 12, 2012 7:14 AM
    Tuesday, July 3, 2012 1:10 PM
    Moderator

All replies

  • Where are you using this code?

    I think what you need is the "Any" method, not "Contains".


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Tuesday, July 3, 2012 1:36 AM
    Moderator
  • I'm using the code within the Projects.cs file (Projects entity).  I also tried the Any, but it doesn't support taking an item as a parameter.

    I've tried the code below as well that I got from another post on using contains, but I am getting a NotSupportedException with the following text "Query operator 'Contains' is not supported.  I've also tried using an array, as well as an IList for the ids to lookup.

    This must be a LightSwitch thing as according to all other sites, this should work with Linq to Sql.

    Here's my code:

    IEnumerable<int> truckSeriesID = new List<int>();

                    truckSeriesID = (from ts in this.ProjectsTruckSeries
                                     where ts.Project_Id == warrantyProjectID
                                     select ts.TruckSeries_Id);

                    // Get warranty claims associated with the current project based on the CREW code and assigned truck series
                    var query = (from wh in this.DataWorkspace.MyDataSource.Claims
                                 where truckSeriesID.Contains(wh.Trucks.TruckSeries.TruckSeries_Id)
                                 orderby wh.ComplaintDate ascending
                                 select wh).Where(wh => wh.CrewCode.CrewCode_Id == this.CrewCode.CrewCode_Id);

    I need to further use the query as a collection throughout other methods in the class.

    Thanks for any help you can provide.


    Sincerely, Ryan Mummey Systems Analyst/Programmer

    Tuesday, July 3, 2012 12:02 PM
  • The reason that Contains doesn't work in your code is because you're dealing with IDataServceQueryable entities. It's a lot more difficult to work directly with these than it is say an IQueryable.

    The ".Any" method is used in two different ways, depending on where you use it. If you were dealing with a navigation collection (added to the entity when you define a 1-Many, or 0..1-Many relationship with another table), you could then supply a lambda expression:

    VB:

    dim filteredEntities = Me.ParentEntity.RelatedCollection.Any(function(x) x.SomeProperty = someValue)

    C#:

    var filteredEntities = this.ParentEntity.RelatedCollection.Any(x => x.SomeProperty = someValue);

    You can also append it to a "Where" clause:

    VB:

    dim exists = Me.DataWorkspace.DataSourceName.EntitySetName.Where(function(x) x.SomeProperty = someValue).Any

    C#:

    dim exists = this.DataWorkspace.DataSourceName.EntitySetName.Where(x => x.SomeProperty = someValue).Any();
    I'm not sure of what you meant by "I need to further use the query as a collection throughout other methods in the class".

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Tuesday, July 3, 2012 12:33 PM
    Moderator
  • I'm storing the query as a private variable within the entity class.  I have many 10 to 15 fields to get computed values for and I didn't want to continue to perform the same query for each computed field calculation.

    Could you further explain how I could get this to work in my code snippet above?  I'm new to LightSwitch and I'm still trying to get through the relationships and Lamda Expression calls.

    Thanks for all the help thus far!


    Sincerely, Ryan Mummey Systems Analyst/Programmer

    Tuesday, July 3, 2012 12:38 PM
  • You're welcome. I'd like to help you get this solved, if I can.

    Try something along the lines of:

    var query = this.DataWorkspace.MyDataSource.Claims
        .where(x => x.truck.ID = "something")
        .OrderBy(x => orderby x.ComplaintDate)
        .select wh);
    var exists = query.Any();

    I have to admit, that I'm not quite "getting" what you're trying to do. This is because, as you say, you're new at C#/LINQ, combined with the fact that my head gets easily muddled these days unfortunately, especially when I don't "get" what's trying to be explained.

    I'm not sure if what I've given you above even makes sense. It late here & I'm tired, as well as muddled, lol.

    If that code doesn't help you find your answer, I'll try to give it a better crack tomorrow (for me down under in the land of OZ).


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    • Marked as answer by Otomii Lu Thursday, July 12, 2012 7:14 AM
    Tuesday, July 3, 2012 1:10 PM
    Moderator