CODEWITHSUNDEEP
sqllinq-to-sql
THX-1138
THX-1138

Reputation: 21730

Linq-to-sql generate single SQL query with distinct()

Following linq statement generates multiple trips to the database, is there a way to change this so it does it in one trip?

db.Members.Distinct().Select(
    m => new { 
        Id = m.Id, 
        PlayTimeSchedules = m.PlayTimeSchedules.Select(pts => pts.DayOfWeek) }
).ToList();

FYI: in this example Distinct is redundant.

There is a one-to-many relationship between a member and his PlayTimeSchedule

I expect something like following to be generated:

select distinct(Members.Id), PlayTimeSchedule.DayOfWeek from Members 
join PlayTimeSchedule on PlayTimeSchedule.MemberId = Members.Id

or am I stuck with creating a view if I want it done in one trip?

EDIT: Here is what above linq generates (I have added .Take(20) for brevity):

    SELECT TOP (20) [t1].[Id]
    FROM (
        SELECT DISTINCT [t0].[Id], [t0].[EMail], [t0].[Username], [t0].[Password], [t0].[GmtOffset], [t0].[LastSeen], [t0].[Notifications], [t0].[EMailVer
    ified], [t0].[ObserveDst], [t0].[Admin], [t0].[AttendedRaidCount], [t0].[MissedRaidCount], [t0].[LedRaidCount], [t0].[CommittedRaidCount]
        FROM [dbo].[Members] AS [t0]
        ) AS [t1]
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT [t0].[DayOfWeek]
    FROM [dbo].[PlayTimeSchedule] AS [t0]
    WHERE [t0].[MemberId] = @x1
    -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    SELECT [t0].[DayOfWeek]
    FROM [dbo].[PlayTimeSchedule] AS [t0]
    WHERE [t0].[MemberId] = @x1
    -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

    +++ 18 more DayOfWeek selections as above

Upvotes: 0

Views: 211

Answers (1)

RPM1984
RPM1984

Reputation: 73102

.Select will not do a join (which is what you want), use .Join or if it makes it easier, use the non-lambda way:

var query = (from m in db.Members
            join s in db.PlayTimeSchedules on m.Id equals s.MemberId
            select new { Id = m.Id, PlayTimeSchedules = s.DayOfWeek }).Distinct();

Upvotes: 1

Related Questions