LintQ for MsSql ends up in wrong Select

LintQ for MsSql ends up in wrong Select

Postby Zero-G. » Sun 26 Apr 2015 08:38

Hey

I have just switched from mySQL to MsSql. - I have created a new LinqConnect Model and tried to fire my first "Select" through the LinQ. End this Ends up in an error...

So: My DataClass:
Code: Select all
Namespace Customers
    Public Class Customer
        Property Id As Guid
        Property Salutation As String
        Property Title As String
        Property Firstname As String
        Property Lastname As String
        Property Street As String
        Property Zip As Int32?
        Property City As String
        Property Insurancenumber As Int16?
        Property Birthdate As Date?
        Property Insurancegroup As Guid?
        Property Deductible As Boolean
        Property Reduceddeductible As Boolean
        Property IsVip As Boolean
        Property AllowAd As Boolean
        Property Information As String
        Property Company As Guid?
        Property Employer As String
        Property Rowversion As Byte()

    End Class
End Namespace


The SQL
Code: Select all
CREATE TABLE dbo.kundenstamm (
  Id               uniqueidentifier,
  geloescht        bit NOT NULL CONSTRAINT DF_kunn_gelo DEFAULT 0,
  Nachname         nvarchar(30),
  Vorname          nvarchar(30),
  Titel            nvarchar(20),
  Strasse          nvarchar(70),
  PLZ              int,
  Ort              nvarchar(50),
  VersNr           bigint,
  Geburtsdatum     date,
  VersGruppe       uniqueidentifier,
  Selbstbehalt     bit,
  VerminderterSB   bit,
  VIP              bit,
  Werbung          bit,
  Info             nvarchar(255) CONSTRAINT DF_kunn_Info DEFAULT '',
  interessenid     bigint,
  firmenid         uniqueidentifier NOT NULL,
  Anrede           int,
  Verhaeltnis1_Id  uniqueidentifier,
  Arbeitgeber      nvarchar(256),
  _rowVersion      timestamp NOT NULL,
  /* Keys */
  CONSTRAINT PK_kunn_Id_kunn PRIMARY KEY (Id),
  /* Foreign keys */
  CONSTRAINT FK_kunn_firm_ID_firm
    FOREIGN KEY (firmenid)
    REFERENCES dbo.firmendaten(ID),
  CONSTRAINT FK_kunn_Verh_Id_Kunv
    FOREIGN KEY (Verhaeltnis1_Id)
    REFERENCES dbo.Kundenverhaeltnis(Id)
)
GO

ALTER TABLE dbo.kundenstamm
  NOCHECK CONSTRAINT FK_kunn_firm_ID_firm
GO

ALTER TABLE dbo.kundenstamm
  NOCHECK CONSTRAINT FK_kunn_Verh_Id_Kunv
GO

CREATE CLUSTERED INDEX CL_optik_kundenstamm
  ON dbo.kundenstamm
  (Nachname, Vorname)
GO

My Query:
Code: Select all
            Return (From locQuery In myDataContext.Kundenstamms Where locQuery.Geloescht = 0 _
                    Select New DataClasses.Customers.Customer With {.Id = locQuery.Id, _
                        .Salutation = locQuery.Anrede, _
                        .Title = locQuery.Titel, _
                        .Lastname = locQuery.Nachname, _
                        .Firstname = locQuery.Vorname, _
                        .Street = locQuery.Strasse, _
                        .Zip = locQuery.PLZ, _
                        .City = locQuery.Ort, _
                        .Insurancenumber = locQuery.VersNr, _
                        .Birthdate = locQuery.Geburtsdatum, _
                        .Insurancegroup = locQuery.VersGruppe, _
                        .Deductible = locQuery.Selbstbehalt, _
                        .Reduceddeductible = locQuery.VerminderterSB, _
                        .IsVip = locQuery.VIP, _
                        .AllowAd = locQuery.Werbung, _
                        .Information = locQuery.Info, _
                        .Company = locQuery.Firmenid, _
                        .Employer = locQuery.Arbeitgeber}).AsQueryable

The generated SQL:
Code: Select all
SELECT [t1].[Id], CAST([t1].[Anrede] AS VarChar) AS [C1], [t1].[Titel], [t1].[Vorname], [t1].[Nachname], [t1].[Strasse], [t1].[PLZ], [t1].[Ort], [t1].[VersNr], [t1].[Geburtsdatum], [t1].[VersGruppe], [t1].[Selbstbehalt], [t1].[VerminderterSB], [t1].[VIP], [t1].[Werbung], [t1].[Info], [t1].[Firmenid], [t1].[Arbeitgeber]
FROM (
    SELECT [t2].[Id], [t2].[Titel], [t2].[Vorname], [t2].[Nachname], [t2].[Strasse], [t2].[PLZ], [t2].[Ort], [t2].[VersNr], [t2].[Geburtsdatum], [t2].[VersGruppe], [t2].[Selbstbehalt], [t2].[VerminderterSB], [t2].[VIP], [t2].[Werbung], [t2].[Info], [t2].[firmenid] AS [Firmenid], [t2].[Arbeitgeber], [t2].[Anrede]
    FROM [dbo].[kundenstamm] [t2]
    WHERE (-[t2].[geloescht]) = @p0
    ) [t1]
WHERE ([t1].[Nachname] = @p1) AND ([t1].[Vorname] = @p2)


The Problem is the where clause the "-"[t2].geloescht...

Please take a look
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Re: LintQ for MsSql ends up in wrong Select

Postby MariiaI » Mon 27 Apr 2015 08:31

Thank you for the report on this, we have reproduced the issue. We will investigate it more clearly and inform you about the results as soon as any are available.
As a temporary workaround, please try using False/True in the query, e.g.:
Code: Select all
Return (From locQuery In myDataContext.Kundenstamms Where locQuery.Geloescht = False _
                    Select New DataClasses.Customers.Customer With {.Id = locQuery.Id, _
               ...

Please tell us if this helps.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LintQ for MsSql ends up in wrong Select

Postby MariiaI » Tue 05 May 2015 11:34

The Problem is the where clause the "-"[t2].geloescht..

We have fixed this issue. The fix will be included in the next public build of LinqConnect. We will inform you when it is available for download.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LintQ for MsSql ends up in wrong Select

Postby MariiaI » Thu 07 May 2015 11:48

New build of LinqConnect 4.4.745 is available!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=31744.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)