LINQ to SQL "complex" select

Question

I have Country=>Ligue=>Team(Name, Score)

I need to select all team Name-Scores from a country.

something like this, does not work )

query = from ligue in myCountry.Ligues, from team in ligue.Teams select name = team.Name, score = team.Score distinct

EDIT:

VB.NET syntax is preferable.

relabeled the question since it turns out not to be about linq-to-sql — jeroenh, Sep 05 '11 at 14:13
@jeroenh I think the question is about LINQ-to-SQL; sorry I didn't provide the syntax you prefer. — Kirk Broadhurst, Sep 05 '11 at 23:07
@kirk if you look at serhio's comments, I believe he's mistaking the 'linq query syntax' with 'linq to sql'. Could be wrong though. Also, I don't have any syntax preference, your answer is perfectly valid and I upvoted it, I just added an answer with the VB.Net syntax, as per the edited question. — jeroenh, Sep 06 '11 at 07:14

Kirk Broadhurst · Answer 1 · 2011-09-05T12:42:56.823

4

You should be able to do a simple Select / SelectMany

context.Countries.Single(c => c.CountryName == "My Country")
    .Ligues.SelectMany(ligue => ligue.Teams
        .Select(team => new { team.Name, team.Score }))
        .Distinct();

edited Sep 05 '11 at 12:42

answered Sep 05 '11 at 12:35

Kirk Broadhurst

27,836
16
104
169

thanks... say, this is not LINQ to SQL, but I will try like this ) and this is not Distinct... – serhio Sep 05 '11 at 12:37
1

@serhio I've added a `Distinct`. This **is** LINQ-to-SQL; just that it uses 'method' syntax rather than 'query' syntax. – Kirk Broadhurst Sep 05 '11 at 12:44
this is Linq, but not Linq-to-SQL, because you don't use any "SQL" syntax. – serhio Sep 05 '11 at 12:47
@serhio wheter it is linq-to-sql or not is not determined by the syntax. It IS linq-2-sql if it works against the linq-to-sql provider. – jeroenh Sep 05 '11 at 13:07
@jeroenh: "if it works against the linq-to-sql provider". So, anything indicates from the code above that it works against the linq-to-sql provider. – serhio Sep 05 '11 at 13:53
@serhio Since your question is labeled linq-to-sql, that can be reasonably assumed. – jeroenh Sep 05 '11 at 14:03
@jeroenh: I should recognize that I (incorrectly) used this therm(linq-to-sql) to indicate the specific SQL syntax in the Linq queries. This why I labeled it like this. My fault. – serhio Sep 05 '11 at 14:08

jeroenh · Answer 2 · 2011-09-06T07:16:31.227

Here's the code from Kirk translated to VB10 extension method syntax:

dim result = context.Countries.Single(Function(c) c.CountryName = "My Country").
               Ligues.SelectMany(Function(ligue) ligue.Teams).
                      Select(Function(team) new with {team.Name, team.Score }).
                      Distinct()

I believe (but am not sure, don't have access to a VB compiler right now) you can write it like this vb.net query syntax

(EDIT my original trial was indeed incorrect, so I corrected the query below:)

dim result = From ligue in myCountry.Ligues
             From team in ligue.Teams
             Select team.Name, team.Score Distinct

score 1 · Accepted Answer · answered Sep 05 '11 at 14:46

1

Using jeroenh's code, that used Kirk's code, here is the working version (VB.NET)

  Dim query =  From ligue In myCountry.Ligues
               From team In ligue.Teams
               Select Name = team.Name, Score = team.Score 
               Distinct

answered Sep 05 '11 at 14:46

serhio

28,010
62
221
374

LINQ to SQL "complex" select

3 Answers3