c# Different Result between Linq and SQL

Question

I try to fetch some data with EF6 from a MSSQL-Database:

IQueryable<Kundentree1> tmp = from f in db.Kundentree1 select f ;

Kundentree1 is a SQL-View on Database.

Because I don't all my result I'm filtering the result:

tmp = tmp.Where(k => k.Strasse
                    .ToLower()
                    .Trim().Contains(search.Strasse
                                           .ToLower()
                                           .Trim()))
         .Distinct();

(this is not the only one, there are some more.. )

And at the end of my Procedure:

var sql = tmp.ToString();
return View(tmp);

In my testing routine tmp gives back 7 Items as result, but item 0 - 6 are the same.

If I use the SqlStatement (SQL) I also get 7 Items, but in this case, the right result, 7 different items.

Any hint where the Problem could be?

Update:

The Full-Generated SQL-CODE (and correct working)

SELECT 
    [Extent1].[Firmenname] AS [Firmenname], 
    [Extent1].[rechtsform] AS [rechtsform], 
    [Extent1].[Strasse] AS [Strasse], 
    [Extent1].[Land] AS [Land], 
    [Extent1].[Postleitzahl] AS [Postleitzahl], 
    [Extent1].[Ort] AS [Ort], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Personen_ID] AS [Personen_ID], 
    [Extent1].[Firmen_ID] AS [Firmen_ID], 
    [Extent1].[Adressen_ID] AS [Adressen_ID], 
    [Extent1].[sex] AS [sex], 
    [Extent1].[Vorwahl] AS [Vorwahl], 
    [Extent1].[Tel1] AS [Tel1], 
    [Extent1].[zahlungskondition] AS [zahlungskondition], 
    [Extent1].[sperrkennzeichen] AS [sperrkennzeichen], 
    [Extent1].[sendkatalag] AS [sendkatalag], 
    [Extent1].[lastkatalog] AS [lastkatalog], 
    [Extent1].[ID] AS [ID], 
    [Extent1].[NACHNAME] AS [NACHNAME], 
    [Extent1].[VORNAME] AS [VORNAME], 
    [Extent1].[ZKTEXT] AS [ZKTEXT], 
    [Extent1].[FAX] AS [FAX], 
    [Extent1].[KNR] AS [KNR], 
    [Extent1].[UID] AS [UID], 
    [Extent1].[firmenbuch] AS [firmenbuch], 
    [Extent1].[email] AS [email], 
    [Extent1].[Lieferant] AS [Lieferant], 
    [Extent1].[PERS_VORNAME] AS [PERS_VORNAME], 
    [Extent1].[PERS_NACHNAME] AS [PERS_NACHNAME], 
    [Extent1].[PERS_sex] AS [PERS_sex], 
    [Extent1].[Titel] AS [Titel], 
    [Extent1].[Zusatz] AS [Zusatz], 
    [Extent1].[Position] AS [Position], 
    [Extent1].[PERS_EMAIL] AS [PERS_EMAIL], 
    [Extent1].[PERSONEN_GRUPPE_ID] AS [PERSONEN_GRUPPE_ID], 
    [Extent1].[NEWSLETTER] AS [NEWSLETTER], 
    [Extent1].[HEROLD_ID] AS [HEROLD_ID], 
    [Extent1].[INFOTEXT] AS [INFOTEXT], 
    [Extent1].[DISG] AS [DISG], 
    [Extent1].[HANDEL] AS [HANDEL], 
    [Extent1].[AdressenTYP] AS [AdressenTYP], 
    [Extent1].[BUNDESLAND] AS [BUNDESLAND], 
    [Extent1].[CANBOX] AS [CANBOX], 
    [Extent1].[KatalogTyp] AS [KatalogTyp], 
    [Extent1].[KatalogBezeichnung] AS [KatalogBezeichnung]
    FROM (SELECT 
    [Kundentree1].[Firmenname] AS [Firmenname], 
    [Kundentree1].[rechtsform] AS [rechtsform], 
    [Kundentree1].[Strasse] AS [Strasse], 
    [Kundentree1].[Land] AS [Land], 
    [Kundentree1].[Postleitzahl] AS [Postleitzahl], 
    [Kundentree1].[Ort] AS [Ort], 
    [Kundentree1].[Name] AS [Name], 
    [Kundentree1].[Personen_ID] AS [Personen_ID], 
    [Kundentree1].[Firmen_ID] AS [Firmen_ID], 
    [Kundentree1].[Adressen_ID] AS [Adressen_ID], 
    [Kundentree1].[sex] AS [sex], 
    [Kundentree1].[Vorwahl] AS [Vorwahl], 
    [Kundentree1].[Tel1] AS [Tel1], 
    [Kundentree1].[zahlungskondition] AS [zahlungskondition], 
    [Kundentree1].[sperrkennzeichen] AS [sperrkennzeichen], 
    [Kundentree1].[sendkatalag] AS [sendkatalag], 
    [Kundentree1].[lastkatalog] AS [lastkatalog], 
    [Kundentree1].[ID] AS [ID], 
    [Kundentree1].[NACHNAME] AS [NACHNAME], 
    [Kundentree1].[VORNAME] AS [VORNAME], 
    [Kundentree1].[ZKTEXT] AS [ZKTEXT], 
    [Kundentree1].[FAX] AS [FAX], 
    [Kundentree1].[KNR] AS [KNR], 
    [Kundentree1].[UID] AS [UID], 
    [Kundentree1].[firmenbuch] AS [firmenbuch], 
    [Kundentree1].[email] AS [email], 
    [Kundentree1].[Lieferant] AS [Lieferant], 
    [Kundentree1].[PERS_VORNAME] AS [PERS_VORNAME], 
    [Kundentree1].[PERS_NACHNAME] AS [PERS_NACHNAME], 
    [Kundentree1].[PERS_sex] AS [PERS_sex], 
    [Kundentree1].[Titel] AS [Titel], 
    [Kundentree1].[Zusatz] AS [Zusatz], 
    [Kundentree1].[Position] AS [Position], 
    [Kundentree1].[PERS_EMAIL] AS [PERS_EMAIL], 
    [Kundentree1].[PERSONEN_GRUPPE_ID] AS [PERSONEN_GRUPPE_ID], 
    [Kundentree1].[NEWSLETTER] AS [NEWSLETTER], 
    [Kundentree1].[HEROLD_ID] AS [HEROLD_ID], 
    [Kundentree1].[INFOTEXT] AS [INFOTEXT], 
    [Kundentree1].[DISG] AS [DISG], 
    [Kundentree1].[HANDEL] AS [HANDEL], 
    [Kundentree1].[AdressenTYP] AS [AdressenTYP], 
    [Kundentree1].[BUNDESLAND] AS [BUNDESLAND], 
    [Kundentree1].[CANBOX] AS [CANBOX], 
    [Kundentree1].[KatalogTyp] AS [KatalogTyp], 
    [Kundentree1].[KatalogBezeichnung] AS [KatalogBezeichnung]
    FROM [dbo].[Kundentree1] AS [Kundentree1]) AS [Extent1]
    WHERE (( CAST(CHARINDEX(LTRIM(RTRIM(LOWER('Salv'))), LTRIM(RTRIM(LOWER([Extent1].[Firmenname])))) AS int)) > 0) AND ( NOT (([Extent1].[Postleitzahl] IS NULL) OR ((LEN([Extent1].[Postleitzahl])) = 0))) AND (( CAST(CHARINDEX(LTRIM(RTRIM('4482')), LTRIM(RTRIM([Extent1].[Postleitzahl]))) AS int)) > 0)

EF requires a primary key. Make sure you have defined such on your db views. See this thread stackoverflow.com/questions/33583309/… — Ivan Stoev
– Ivan Stoev, Commented Dec 17, 2015 at 14:43
Can you show some example input and output? What do you mean by "If I use the SqlStatement (SQL)" exactly? — CodeCaster
– CodeCaster, Commented Dec 17, 2015 at 14:57
Try posting your entire Linq query that doesn't work, and your T-SQL that does work. Have you tried debugging to look at the sql that EF is generating to see if it is correct? — Johnie Karr
– Johnie Karr, Commented Dec 17, 2015 at 15:10
What relational world sees as equal it not necessary the same for object world. (One of the reasons why ORM exists). For this Distinct() operation to succeed I suppose you will need to implement IEquatable<Kundentree1> and override Equals and GetHashCode to define a business signature for your domain objects. — E-Bat
– E-Bat, Commented Dec 17, 2015 at 15:20
@IvanStoev: the view is a join over 3 tables. a, b, c. it is Possible that table c contains no result ergo the key of table c is null. so if i want to create a key in the model over the 3 table keys it would not work ... — Gerd Vtw
– Gerd Vtw, Commented Dec 17, 2015 at 15:21

Gerd Vtw · Accepted Answer · 2015-12-17 15:51:14Z

2

thanks to Ivan Stoev:

Creating a key on the view on database works

in my case it is a fake key (uniqueidentifier newID()) which i need to work

answered Dec 17, 2015 at 15:51

Gerd Vtw

514 bronze badges

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

Collectives™ on Stack Overflow

c# Different Result between Linq and SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related