Linq query generates different result in SQL

Question

I wrote this left join in LINQ and I only selected PersonId from SHPerson table, but in SQL Server profiler, I got PersonId from SHPFA table too.

 var spfQuery = from n in shHPFARepository.GetAll()
                                      .AsNoTracking()
                                      .Where(t => t.ShareCount > 0)
                group n by new { n.PersonId } into nGroup
                select new 
                { 
                    nGroup.Key.PersonId, 
                    TotalhareCount = nGroup.Sum(t => t.ShareCount) 
                };                                                                                       

 var query = (from sp in SHPersonRepository.GetAll().AsNoTracking()
              join spf in spfQuery on sp.Id equals spf.PersonId 
              select new SHPOutputDto
             {
                 PersonId = sp.PersonId,
                 ShareCount = spf.TotalShareCount,
                 IsShareHolder=true
             });

var resultDto = await query.ToListAsync();

I don't want SHPFA.PersonId ([t0].[PersonId]) being selected in query.

SELECT [t0].[PersonId], [t0].[TotalShareCount], [e].[PersonId]
FROM [SHolders].[SHPerson] AS [e]
INNER JOIN 
    (SELECT [t].[SHPersonId], SUM([t].[ShareCount]) AS [TotalShareCount]
     FROM [SHolders].[SHPFA] AS [t]
     WHERE [t].[ShareCount] > 0
     GROUP BY [t].[PersonId]) AS [t0] ON [e].[Id] = [t0].[PersonId]

Richard Baldauf · Accepted Answer · 2020-05-27 06:49:57Z

0

Are you would like to remove it from the sub select inner join or want to remove it from the final select result? Because seems like you are using two similar columns [PersonId] and [SHPersonId]

If this is only a typo thing then you should use [SHPersonId] in your first query, like this:

var spfQuery = from n in shHPFARepository.GetAll()
                                  .AsNoTracking()
                                  .Where(t => t.ShareCount > 0)
            group n by n.SHPersonId into nGroup
            select new 
            { 
                SHPersonId = nGroup.Key, 
                TotalhareCount = nGroup.Sum(t => t.ShareCount) 
            };

P.S.: don't forget to modify the second query by renaming PersonId to SHPersonId

answered May 27, 2020 at 6:49

Richard Baldauf

1,1282 gold badges10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hussein zakizadeh Over a year ago

I want to remove [t0].[PersonId] from the final select result

Richard Baldauf Over a year ago

Did you try removing the PersonId = sp.PersonId line from then final query select new SHPOutputDto? I have a feeling we are misunderstanding each other...

hussein zakizadeh Over a year ago

no. the SHPOutputDto is what I want to see in output but I'm getting two PersonId in SQL Server profiler, the first PersonId is for SHPFA table([t0].[PersonId]) and the second PersonId is for SHPerson table([e].[PersonId]). I don't want the first PersonId being selected([t0].[PersonId]).

Richard Baldauf Over a year ago

gotcha! I was able to understand the REAL problem... :D this problem seems to be a SQL profiler bug. Maybe the issue is related to the EDMX. Could you try removing and readding the table inside the EDMX? I know that is not so easy but it's worth a shot!

StepUp · Accepted Answer · 2020-05-27 06:56:05Z

0

As an alternative, you can always use raw sql query:

using (var ctx = new YourContext())
{

    var spfQuery = ctx.Database.SqlQuery<YourModel>("SELECT
         [t0].[TotalShareCount]
       , [e].[PersonId]
       FROM [SHolders].[SHPerson] AS [e]
       INNER JOIN 
          (SELECT [t].[SHPersonId], SUM([t].[ShareCount]) AS [TotalShareCount]
           FROM [SHolders].[SHPFA] AS [t]
           WHERE [t].[ShareCount] > 0
           GROUP BY [t].[PersonId]) AS [t0] ON [e].[Id] = [t0].[PersonId]"
    ).ToList();
}

answered May 27, 2020 at 6:56

StepUp

38.5k16 gold badges95 silver badges159 bronze badges

Collectives™ on Stack Overflow

Linq query generates different result in SQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related