Queries with "Contains" broken after upgrade from RC2 to RTM

[dvdobrovolskiy]

Query with lots of Where now broken and generates "Incorrect syntax near the keyword 'IN'." error.

Query is:

            model.Zayavkas = _context.Zayavkas.Select(s=> new Zayavka
            {
                Apartment = s.Apartment,
                ApartmentId = s.ApartmentId,
                Block = _context.Blocks.SingleOrDefault(d => d.BlockId == s.BlockId),
                BlockId = s.BlockId,
                Description = s.Description,
                DispatcherPhone = s.DispatcherPhone,
                EndDate = s.EndDate,
                Reason = _context.Reasons.SingleOrDefault(b => b.ReasonId == s.ReasonId),
                ReasonId = s.ReasonId,
                Reasontmp = s.Reasontmp,
                Responsable = _context.Users.SingleOrDefault(b => b.Id == s.ResponsableId),
                ResponsableId = s.ResponsableId,
                StartDate = s.StartDate,
                Worker = _context.Users.SingleOrDefault(b => b.Id == s.WorkerId),
                WorkerId = s.WorkerId,
                ZayavkaId = s.ZayavkaId,
                ZayavkaStatus = s.ZayavkaStatus,
                CreatedDate = s.CreatedDate,
                CreatorId = s.CreatorId,
                EditedDate = s.EditedDate,
                ModifierId = s.ModifierId,
                Creator = _context.Users.SingleOrDefault(c=>c.Id == s.CreatorId),
                Modifier = _context.Users.SingleOrDefault(m => m.Id == s.ModifierId),
                MainPhone = s.MainPhone,
                SecondaryPhone = s.SecondaryPhone,
                TertiaryPhone = s.TertiaryPhone
            })
            .Where(w => model.status.Count() > 0 ? model.status.Contains(w.ZayavkaStatus) : true)
            .Where(w => model.type.Count() > 0 ? model.type.Contains(_context.Reasons.SingleOrDefault(b => b.ReasonId == w.ReasonId).ZayavkaType) : true)
            .Where(w => model.BlockId.Count() > 0 ? model.BlockId.Contains(w.BlockId) : true)
            .Where(w => Responsable ? w.ResponsableId == user.Id: true)
            .Where(w => Worker ? w.WorkerId == user.Id : true)
            .Where(w => w.CreatedDate >= _start)
            .Where(w => w.CreatedDate < _end )
            .OrderByDescending(o => o.ZayavkaId)
            .Take(DefaultPageSize)
            .ToList();

worked fine before upgrade. now I have
SqlException: Incorrect syntax near the keyword 'IN'.
Incorrect syntax near '@__Responsable_6'.
Incorrect syntax near '@__Worker_8'.

and it is just random. Commenting out all Where clauses and it works. adding several WHERE and it breaks. adding random where I got random mistakes mostly Incorrect syntax near the keyword 'IN'

[gdoron]

Does writing all in one big Where work?

[dvdobrovolskiy]

Sorry, already rolled back. Need time to prepare test solution

[rowanmiller]

Hey,

We'll need something that we can run in order to reproduce and debug this one. Trying to reverse engineer your model from the query isn't really feasible.

When filing issues, if possible, try to follow the template we provide - which includes the following section:

Steps to reproduce

Ideally include a complete code listing that we can run to reproduce the issue.
Alternatively, you can provide a project/solution that we can run.

~Rowan

[dvdobrovolskiy]

Understand that. Here is the solution

http://dobrovolskiy.com/Dispatcher.zip

Unfortunately due to an other weird error in migrations I cannot apply migrations to empty DB.
Column 'Name' in table 'Permissions' is of a type that is invalid for use as a key column in an index.

Column 'Name' in table 'Permissions' is of type 'string'!

Might be you can solve it.

[dvdobrovolskiy]

Also I can supply you with SQL generated by that code (traced in sql profiler)

exec sp_executesql N'SELECT [s.Apartment].[ApartmentId], [s.Apartment].[BlockId], [s.Apartment].[DenaryPhone], [s.Apartment].[Entrance], [s.Apartment].[Floor], [s.Apartment].[MainPhone], [s.Apartment].[Number], [s.Apartment].[OctonaryPhone], [s.Apartment].[QuaternaryPhone], [s.Apartment].[QuinaryPhone], [s.Apartment].[SecondaryPhone], [s.Apartment].[SenaryPhone], [s.Apartment].[SeptenaryPhone], [s.Apartment].[TertiaryPhone], [s].[ReasonId], [s].[BlockId], [s].[ResponsableId], [s].[WorkerId], [s].[CreatedDate], [s].[ApartmentId], [s].[Description], [s].[DispatcherPhone], [s].[EndDate], [s].[Reasontmp], [s].[StartDate], [s].[ZayavkaId], [s].[ZayavkaStatus], [s].[CreatorId], [s].[EditedDate], [s].[ModifierId], [s].[MainPhone], [s].[SecondaryPhone], [s].[TertiaryPhone]
FROM [Zayavkas] AS [s]
INNER JOIN [Apartments] AS [s.Apartment] ON [s].[ApartmentId] = [s.Apartment].[ApartmentId]
WHERE (((((CASE
WHEN @__Count_0 > 0
THEN [s].[ZayavkaStatus] IN (0, 1, 3) ELSE CAST(1 AS BIT)
END = 1) AND (CASE
WHEN @__Count_4 > 0
THEN 0 = 1 ELSE CAST(1 AS BIT)
END = 1)) AND (((@__Responsable_6 = 1) AND ([s].[ResponsableId] = @__user_Id_7)) OR (@__Responsable_6 <> 1))) AND (((@__Worker_8 = 1) AND ([s].[WorkerId] = @__user_Id_9)) OR (@__Worker_8 <> 1))) AND ([s].[CreatedDate] >= @___start_10)) AND ([s].[CreatedDate] < @___end_11)
ORDER BY [s].[ZayavkaId] DESC',N'@__Count_0 int,@__Count_4 int,@__Responsable_6 bit,@__user_Id_7 int,@__Worker_8 bit,@__user_Id_9 int,@___start_10 datetime2(7),@___end_11 datetime2(7)',@__Count_0=3,@__Count_4=0,@__Responsable_6=0,@__user_Id_7=1,@__Worker_8=0,@__user_Id_9=1,@___start_10='2016-06-02 00:00:00',@___end_11='2016-07-03 00:00:00'

[dvdobrovolskiy]

Year, ok. Found problem

Error appeared then I use conditional construction like
.Where(w => model.status.Count() > 0 ? model.status.Contains(w.ZayavkaStatus) : true)
then
.Where(w => model.status.Contains(w.ZayavkaStatus) ) works fine.

For some reason EF Core translate model.status.Count() > 0 ? to SQL query as
CASE
WHEN @__Count_0 > 0
THEN [s].[ZayavkaStatus] IN (0, 1, 3) ELSE CAST(1 AS BIT)
END = 1)

And this started after upgrade to RTM. I deliberately checked old version (published on server)
and there is no such construction
For example broken sql trace (previous post) in old version with same params translated as
exec sp_executesql N'SELECT TOP(@__p_3) [s.Apartment].[ApartmentId], [s.Apartment].[BlockId], [s.Apartment].[DenaryPhone], [s.Apartment].[Entrance], [s.Apartment].[Floor], [s.Apartment].[MainPhone], [s.Apartment].[Number], [s.Apartment].[OctonaryPhone], [s.Apartment].[QuaternaryPhone], [s.Apartment].[QuinaryPhone], [s.Apartment].[SecondaryPhone], [s.Apartment].[SenaryPhone], [s.Apartment].[SeptenaryPhone], [s.Apartment].[TertiaryPhone], [s].[ApartmentId], [s].[BlockId], [s].[Description], [s].[DispatcherPhone], [s].[EndDate], [s].[ReasonId], [s].[Reasontmp], [s].[ResponsableId], [s].[StartDate], [s].[WorkerId], [s].[ZayavkaId], [s].[ZayavkaStatus], [s].[CreatedDate], [s].[CreatorId], [s].[EditedDate], [s].[ModifierId], [s].[MainPhone], [s].[SecondaryPhone], [s].[TertiaryPhone]
FROM [Zayavkas] AS [s]
INNER JOIN [Apartments] AS [s.Apartment] ON [s].[ApartmentId] = [s.Apartment].[ApartmentId]
WHERE [s].[ZayavkaStatus] IN (0, 1, 3) AND (CASE
WHEN @__Count_1 > 0
THEN [s].[ZayavkaStatus] IN (0, 1, 3) ELSE CAST(1 AS BIT)
END = 1)',N'@__p_3 int,@__Count_1 int',@__p_3=10,@__Count_1=3

[dvdobrovolskiy]

also some play on code
.Where(w => true ? model.status.Contains(w.ZayavkaStatus) : true) // works
.Where(w => cntstatus ? model.status.Contains(w.ZayavkaStatus) : true) // does not even if cntstatus is true

.Where(w => (cntstatus == true) ? model.status.Contains(w.ZayavkaStatus) == true : true) // works
.Where(w => (cntstatus == true) ? model.status.Contains(w.ZayavkaStatus) : true) // does not!

[dvdobrovolskiy]

sorry for flood but seems like adding "== true" to Contains method (returning also bool) works

[maumar]

fixed in 80b7c08