How to join multiple tables using LINQ-to-SQL?
- by user603245
Hi!
I'm quite new to linq, so please bear with me.
I'm working on a asp.net webpage and I want to add a "search function" (textbox where user inputs name or surname or both or just parts of it and gets back all related information). I have two tables ("Person" and "Application") and I want to display some columns from Person (name and surname) and some from Application (score, position,...). I know how I could do it using sql, but I want to learn more about linq and thus I want to do it using linq.
For now I got two main ideas:
1.)
var person = dataContext.GetTable<Person>();
var application = dataContext.GetTable<Application>();
var p1 = from p in Person
where(p.Name.Contains(tokens[0]) || p.Surname.Contains(tokens[1]))
select new {Id = p.Id, Name = p.Name, Surname = p.Surname}; //or maybe without this line
//I don't know how to do the following properly
var result = from a in Application
where a.FK_Application.Equals(index) //just to get the "right" type of application
//this is not right, but I don't know how to do it better
join p1
on p1.Id == a.FK_Person
2.) The other idea is just to go through "Application" and instead of "join p1 ..." to use
var result = from a in Application
where a.FK_Application.Equals(index) //just to get the "right" type of application
join p from Person
on p.Id == a.FK_Person
where p.Name.Contains(tokens[0]) || p.Surname.Contains(tokens[1])
I think that first idea is better for queries without the first "where" condition, which I also intended to use. Regardless of what is better (faster), I still don't know how to do it using linq. Also in the end I wanted to display / select just some parts (columns) of the result (joined tables + filtering conditions).
I really want to know how to do such things using linq as I'll be dealing also with some similar problems with local data, where I can use only linq.
Could somebody please explain me how to do it, I spent days trying to figure it out and searching on the internet for answers.
Thank you for your time.
