Linq Sql Query result is different from the SQL query run in the database

Question

When I run the query in C# code using Linq the result returned is different from the sql query run in sql server

SQL query

SELECT TOP (1000) [Teamid]
  ,[TeamName]
  ,[TemplateId]
  ,[TemplateName]
 FROM [MPFT_SendIT].[dbo].[VMTemplate]
 where
 Teamid=1

Result

SQL Query of the VMTemplate View

     SELECT        dbo.Team.Id AS Teamid, dbo.Team.TeamName, 
          dbo.MessageTemplate.Id AS TemplateId, 
            dbo.MessageTemplate.TemplateName
         FROM        dbo.Team INNER JOIN
                     dbo.TemplateLookup ON dbo.Team.Id = 
                    dbo.TemplateLookup.TeamId INNER JOIN
                    dbo.MessageTemplate ON dbo.TemplateLookup.TemplateId = 
                    dbo.MessageTemplate.Id
                     where
                     TeamId= 1

Result

Linq SQL

 var teamid = _db.TeamLookups.Where(i => i.UserId == 20).Select(x => 
  x.TeamId).ToList(); // teamid return value is 1

 ViewBag.messageTemplate = _db.VMTemplates.Where(i => 
 teamid.Contains(i.Teamid));

Linq query only returns one record line 1 of the sql query instead of 2 records as expected. Any help on how to solve this issue ?

Have you verified _for sure_ that the `SSMS` query and the `Linq` query are selecting from the same database? — user1429080, Apr 16 '19 at 09:22
Why is SQL using : "Teamid=1" while linq is using "i.UserId == 20"? — jdweng, Apr 16 '19 at 09:27
@jdweng I am using userid = 20 in teamlookup table to get the Teamid and I use that TeamId in second query to display the data in VMTemplate. I verified the both query are run from the same database — Eric Mbiada, Apr 16 '19 at 09:34
Have you made any changes to the database after you built the `Linq` model? — user1429080, Apr 16 '19 at 09:40
Ca you run exactly same query in SSMS and get same results? Right now you are not comparing APPLES with APPLES, instead you are comparing APPLES with ORANGES. — jdweng, Apr 16 '19 at 09:49
Suppose you team has two player. The SQL will return the results for both team members while linq is returning results for only one team member. — jdweng, Apr 16 '19 at 10:07
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would prove useful. — NetMage, Apr 17 '19 at 18:02

Rahul · Answer 1 · 2019-04-16T09:38:11.713

0

Why Contains()? You should use equality operator rather

_db.VMTemplates.Where(i => teamid == i.Teamid).ToList();

Per your comment, then your Linq expression should work just fine. Add a ToList() to it

ViewBag.messageTemplate = _db.VMTemplates.Where(i => 
 teamid.Contains(i.Teamid)).ToList();

edited Apr 16 '19 at 09:38

answered Apr 16 '19 at 08:28

Rahul

76,197
13
71
125

I am using contain because the var teamid = _db.TeamLookups.Where(i => i.UserId == 20).Select(x => x.TeamId).ToList() can return more than one value – Eric Mbiada Apr 16 '19 at 08:29
@EricMbiada so you want all the teamids or only teamid=1? – Rahul Apr 16 '19 at 08:31
I want all the result from VMTemplates where i.Teamid = teamid I use teamid= 1 in this case because I did not want to return many record – Eric Mbiada Apr 16 '19 at 08:37
@EricMbiada, how do you consume `ViewBag.messageTemplate` ? – akos.pinter Apr 16 '19 at 08:59
@akos.pinter I am passing the result of the viewbag to a dropdown Html.DropDownList("MessageTemplate", new SelectList(ViewBag.messageTemplate, "TemplateId", "TemplateName"), "Select a Message Template") – Eric Mbiada Apr 16 '19 at 09:03

Linq Sql Query result is different from the SQL query run in the database

1 Answers1