Convert Join SQL to LINQ-to-SQL Method Syntax

Question

I have a repository for a MVC 4 Web API to retrieve data from database using Linq to SQL Method Syntax. The code I have so far (its not much) for the a utility:

public IQueryable<Vehicle> Vehicles(int driverId_Vehicles)
{
    ...?...
}

I've put together an SQL query that would get the data from the database:

SELECT VEHICLE.*
FROM Vehicle
INNER JOIN DriverHabit
ON VEHICLE.Id=DriverHabit.VehicleId
WHERE DriverHabit.Id = driverId

I need assistance translating this query into the LINQ method syntax to return the IQueryable.

Gert Arnold · Accepted Answer · 2013-02-14 09:31:35Z

1

It looks like the association Vehicle - DriverHabit is 1:n. There should be a navtigation property DriverHabits in your Vehicle class, so you don't have to join. Just access the navigation property.

answered Feb 14, 2013 at 9:31

Gert Arnold

110k36 gold badges219 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

inspile Over a year ago

DriverHabit is a join table between Driver and Vehicle. A Driver does have many DriverHabits, but each DriverHabit has one and only one Vehicle. In the DriverHabit table, DriverId is a Foreign Key from the Id field in Driver, and VehicleId is a Foreign Key from the Vehicle table.

Gert Arnold Over a year ago

OK, so go for it! Use the navigation property and let linq-to-sql worry about joins.

inspile Over a year ago

Oh! I followed a tutorial on using Entity Framework and I didn't realize, but I already have the navigation property setup, I just didn't know how to use it. Thanks for the tip!

RAM · Accepted Answer · 2013-06-14 15:46:38Z

0

SELECT VEHICLE.* FROM Vehicle INNER JOIN DriverHabit ON VEHICLE.Id=DriverHabit.VehicleId WHERE DriverHabit.Id = driverId

var vehicle = from v in _context.vehicle              
          join dh in _context.driverhabit on v.Id equals dh.VehicleId
          where dh.Id = driverId
          select { v, dh }

return vehicle.ToList();

edited Jun 14, 2013 at 15:46

answered Feb 14, 2013 at 6:58

RAM

911 silver badge12 bronze badges

1 Comment

inspile Over a year ago

There is no "select" after the where, and ToList() should be changed to Queryable(), but this is in the right direction. This, however, is SQL syntax, not method syntax.

Collectives™ on Stack Overflow

Convert Join SQL to LINQ-to-SQL Method Syntax

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related