LintQ for MsSql ends up in wrong Select

[Zero-G.]

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

[MariiaI]

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]

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]

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.