Wanted to share this since it gave me so much trouble figuring out. It’s a simple SQL query ported to LINQ to SQL that joins two tables to return a filtered listed of data.
Here are the tables from my schema
Here is a basic SQL statement I could fire to retrieve my user videos.
select * from video v, user_videos uv where v.vid = uv.vid and uv.uid = 2
Here is how you would run the same query using .net’s LINQ to SQL.
// create DB connection
var db = new DBCONN();
// run query
List<video> uvids = (
from c in db.video
join o in db.user_videos
on c.vid equals o.vid
where o.uid == 2
select c
).ToList();
This query differs slightly from the screenshot below because I used it in a WCF Service.
The variable DBCONN is my database connection that I established when mapping my DB. If you are not familiar with how to set this up, use the Visual Studio’s “Add the ADO.NET Entity Data Model” wizard. With your .net project open, right click your project, left click on “Add the ADO.NET Entity Data Model”. This wizard will walk you through setting up everything you need to setup your DB model file ( edmx ), as well as setting up your database connection and saving it in web.config.
Jesse Liberty did a simple tutorial that uses this wizard in a WCF service application.
I hope this helps somebody out.
EXCELLENT.I'VE SEARCHED ALOT OF SITES BUT COULDN'T GET ANYTHING NEAR TO THIS.
THANKS
it will give you only from video table.but i need to get a column from both the tables.is there any way?
Hello Shirass,
If you look at the first line of this statement "List<video> uvids" you'll see it's returning a list of Video entities. If you added a column from another table, that wouldn't be a Video anymore. So if you're using LINQ to SQL or LINQ to Entities and need data from multiple tables, I would suggest setting up a new entity type and mapping it to columns from your two tables. I hope that makes sense.
That was a useful post,I await many more post from you.
If you look at the first line of this statement "List<video> uvids" you'll see it's returning a list of Video entities. If you added a column from another table, that wouldn't be a Video anymore. So if you're using LINQ to SQL or LINQ to Entities and need data from multiple tables, I would suggest setting up a new entity type and mapping it to columns from your two tables. I hope that makes sense.
can u plz show us how
please bent over i will show how