How to join multiple tables using LINQ-to-SQL?

Posted by user603245 on Stack Overflow See other posts from Stack Overflow or by user603245
Published on 2011-02-04T14:43:12Z Indexed on 2011/02/04 15:26 UTC
Read the original article Hit count: 204

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about linq-to-sql

Related posts about table