CODEWITHSUNDEEP
linq-to-sql
Tomas
Tomas

Reputation: 18097

Linq to Sql join two tables

I have such database structure (1 - one, 0 - many)

Product 1->0 Orders 0->1 Users

I need to find all users for specified product, I have started writing linq query but don't know how to finish it.

public IQueryable<User> GetUsers(int productId)
{            
  return _db.Products.First(p => p.Id == productId)....
}

Upvotes: 2

Views: 6218

Answers (2)

Peter Majeed
Peter Majeed

Reputation: 5352

First things first, the First method will only return the first item that meets your criteria, and since you want to return an IQueryable of Users, this is probably not what you want.

Second, I think the easiest way to get started with Linq-to-SQL is to use query notation, especially when you're dealing with joins, which don't look too pretty when using lambda expressions.

public IQueryable<User> GetUsers(int productId)
{
    return from p in _db.Products
           join o in _db.Orders on p.Id equals o.productId
           join u in _db.Users on o.userId equals u.userId
           where p.productId == productId
           select u;
}

Upvotes: 3

PaulG
PaulG

Reputation: 590

Something like this should work (written in VB.net):

Dim users() = (from x in _db.Users
             Join y in _db.Orders on y.userid equals x.userid
             Join z in _db.Product on z.productid equals y.productid
             where z.productname = "ProductName"
             select x.firstname,x.lastname,x.email,e.tel).toarray()

I've guessed at the fields in the tables, but this will give you the firstname, lastname email and telephone number of users that have ordered a certain product.

Thanks

Paul.

Upvotes: 1

Related Questions