locked
How to filter data from two tables RRS feed

  • Question

  • User-1526035670 posted

    Hi 

    Im using Linq to SQL. Below is a sample of my tables structure (2 tables):

    Customer: Id, Name
    VehicleType: Id, Name
    Vehicles: Id, CustomerId, VehicleTypeId

    Im using a Linq to Sql query where Im returning a Customer type but also would like to return the Customer type by passing in/filtering by a single vehicle. I tried this but didnt compile

    query = ctx.Customers.Where(c => c.Vehicles.Contains(c.id));

    The error i received is "cannot convert from 'int' to 'Vehicle' "

    I then attempted with

    var veh = ctx.Vehicles.FirstOrDefault(v => v.VehicleTypeId == 1);
    query = ctx.Customers.Where(c => c.Vehicles.Contains(veh));

    Which compiled but doesnt return all the data? How could i return the customer type with all associated vehicles?

    Thanks

    Tuesday, June 23, 2020 2:32 PM

Answers

All replies

  • User1120430333 posted

    How could i return the customer type with all associated vehicles?

    You need to learn how to use the Include statment for getting data from related entity.

    https://docs.microsoft.com/en-us/ef/ef6/querying/related-data

    You use a Linq Join.

    https://www.tektutorialshub.com/entity-framework/join-query-entity-framework/

    Tuesday, June 23, 2020 3:06 PM
  • User-1526035670 posted

    Hi

    Sadly this project doesnt have EF but i digged around with the Join statement and might be missing a thing or two. It compiles but i just need to return this as a Customer. As i understand this is an anonymous type (cs below for testing) so i couldnt bind it to query (from above code) as thats of type IQueryable<Customer> so 

    var cs = ctx.Customers.Join(
                        ctx.vehicles, c => c.id, v => v.VehicleId,
                        ((customer, vehicle) => new
                        {
    
                        }));

    this is my current attempt but if i could bind this as IQueryable<Customer> hopefully that should resolve.

    Alternatively i could get all the Vehicles by id and then have a foreach loop to get the Customer and convert it which i feel is a little long winded.

    Thanks again

    Tuesday, June 23, 2020 3:25 PM
  • User1120430333 posted

    You would need to use a Linq projection with a join using a custom type, a class, projecting the object 

    https://csharp-station.com/Tutorial/Linq/Lesson02

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 24, 2020 1:12 PM