Linq to SQL Joins

Question

Table Layout

I am new to Linq to SQL and I am looking for a concise way to get a list of Summoners from the SummonerTable based upon a primary key value "UserId" from the UserSummonerTable. Each of the Summoner1-Summoner5id's in the UserSummonerTable are Foreign Keys referencing the Primary Key "Id" from the the SummonerTable. I want to use all of the SummonerIds from the UserSummonerTable to grab all of the corresponding Summoners in the SummonerTable.

Thanks in advance!

EDIT: I have made objects for each of the tables referencing all of the columns and can grab each table individually but would like a way to do it all in one Select.

EDIT: This gets me close to what I want, although I would like to wrap the results into an Object "Summoner" with Id and Server attributes and it is very messy. I am playing around with some of the more concise forms people have suggested to no avail but I will keep reading and trying! If anyone has any suggestions they are much appreciated. As soon as I get it working as intended I will mark an answer, or is it good practice to give people credit for getting me on the right track before I figure it out fully?

MyDataContext dc = new MyDataContext(ConfigurationManager.ConnectionStrings["StatServer"].ConnectionString);
        var t = (from user in dc.GetTable<UserSummoner>()
                where (user.UserId.Equals(id))
                select user).First();
        var summoners = (from summoner in dc.GetTable<SummonerTable>()
                         where (summoner.Id == t.Summoner1Id || summoner.Id == t.Summoner2Id || summoner.Id == t.Summoner3Id || summoner.Id == t.Summoner4Id || summoner.Id == t.Summoner5Id)
                         select new { summoner.Id, summoner.Server }).ToList();

What's the reason for 5 foreign keys? Looks like a very bad idea. — Gert Arnold, Apr 03 '14 at 23:35

Trikaldarshiii · Answer 1 · 2014-03-06T04:06:04.967

1

There are a lot of post here and also you will find on google you should do much search before asking

here is

var q = 
   from u in UserSummonerTable 
    join s1 in SummonerTable on u.summoner1ID equals s1.id
    join s2 in SummonerTable on u.summoner2ID equals s2.id
    join s3 in SummonerTable on u.summoner3ID equals s3.id
    join s4 in SummonerTable on u.summoner4ID equals s4.id
    join s5 in SummonerTable on u.summoner5ID equals s5.id
    select new { u.id, s1.Name,s2.Name,s3.Name,s4.Name,s5.Name};

edited Mar 06 '14 at 04:06

answered Mar 06 '14 at 03:49

Trikaldarshiii

11,174
16
67
95

However I must add that this defeats the point of an ORM. You should use associations. Not to mention you should use EF rather than L2S on new dev. – Aron Mar 06 '14 at 04:04
@Aron would you like to explain your code words – Trikaldarshiii Mar 06 '14 at 04:06
`var q = Context.UserSummonerTable.Include(x => x.Summoner1).Include(x => x.Summoner2)...`. However it now occurs to me that even this is a stupid idea. You should use a 1-to-many relationship. `var q = Context.User.Include(x => x.Summoners)` – Aron Mar 06 '14 at 04:09

score 0 · Answer 2 · answered Mar 06 '14 at 03:40

0

var q = 
    (from s in SummonerTable 
    join u in UserSummonerTable on s.Id equals u.UserId 
    select new { s, u }).ToList();

This might need some minor tweaking to get your intended result, but this should roughly do it.

Also, if you're interested in learning LINQ-related stuff, MSDN has this, which is awesome: http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

answered Mar 06 '14 at 03:40

Firoze Rakib

132
1
4

Thanks for the reply, sadly I don't think this is going to work. What I am trying to do is something like this. var q = (from s in dc.SummonerTables join u in dc.UserSummonerTables on s.Id equals (u.Summoner1Id||u.Summoner2Id|||u.Summoner2Id|||u.Summoner3Id|||u.Summoner4Id|||u.Summoner5Id) select new { s, u }).ToList(); Thanks for the link I will read it over! – Brian Loomis Mar 06 '14 at 03:48

Linq to SQL Joins

2 Answers2