Compiled Query Cache Bug

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
Action
Posts: 4
Joined: Fri 08 Aug 2014 18:42

Compiled Query Cache Bug

Post by Action » Fri 08 Aug 2014 19:12

I have a method that basically calls a query similar to what is in the first code block. Where identifier is one of the parameters passed into the method. If I call the method multiple times it always seems to return values from the first identifier passed in unless I call the Clear method on the query cache. I have created a db backup of this sample along with a windows forms project that can duplicate this behavior that can be provided upon request.

Current Information:
OS: Windows 7 Professional
DB: MS SQL Server 2012
VS: Visual Studio 2013 Professional
LinqConnect Version: 4.4.553.0

Code: Select all

var res = (from i in context.Identifiers
           join e in context.Entries on i.IdentifierId equals e.IdentifierId
           join l in context.Locations on e.EntryId equals l.EntryId
           join v in context.Values on l.LocationId equals v.LocationId
           join t in context.Types on v.TypeId equals t.TypeId
           where i.Name == identifier
           orderby e.EntryIndex, l.Location1, t.Name, e.EntryDateTime ascending
           group new
           {
               Type = t.Name,
               Value = v.Value1
           } by l.Location1 into g
           select g);
Here is the SQL script when identifier=ID1

Code: Select all

2014-08-08 13:34:24.05 spid51      ODS Event: Remote_ods : Xact 0 ORS#: 1, connId: 0
2014-08-08 13:34:24.05 spid51      Xact BEGIN for Desc: 3300000003
2014-08-08 13:34:24.05 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=948,Len=948,Pxvar Value=SELECT [t1].[Location] AS [Location1]
FROM (
    SELECT [t4].[Location]
    FROM [dbo].[identifiers] [t2]
    INNER JOIN [dbo].[entries] [t3] ON [t2].[IdentifierId] = [t3].[IdentifierId]
    INNER JOIN [dbo].[locations] [t4] ON [t3].[EntryId] = [t4].[EntryId]
    INNER JOIN dbo.[values] [t5] ON [t4].[LocationId] = [t5].[LocationId]
    INNER JOIN [dbo].[types] [t6] ON [t5].[TypeId] = [t6].[TypeId]
    WHERE [t2].[Name] = @p0
    ) [t1]
GROUP BY [t1].[Location]
2014-08-08 13:34:24.05 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=28,Len=28,Pxvar Value=@p0 varchar(3)
2014-08-08 13:34:24.05 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:34:24.05 spid51          IPC Name: sp_executesql
2014-08-08 13:34:24.05 spid51      ODS Event: execrpc : Xact 3300000003 ORS#: 1, connId: 0
2014-08-08 13:34:24.05 spid51      ODS Event: Logout connection 51
2014-08-08 13:34:24.05 spid51      ODS Event: Login connection 51
2014-08-08 13:34:24.05 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:34:24.05 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:34:24.05 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:34:24.05 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc1
2014-08-08 13:34:24.05 spid51          IPC Name: sp_executesql
2014-08-08 13:34:24.05 spid51      ODS Event: execrpc : Xact 3300000003 ORS#: 1, connId: 0
2014-08-08 13:34:24.05 spid51      ODS Event: Logout connection 51
2014-08-08 13:34:24.05 spid51      ODS Event: Login connection 51
2014-08-08 13:34:24.05 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:34:24.05 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:34:24.05 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:34:24.05 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc2
2014-08-08 13:34:24.05 spid51          IPC Name: sp_executesql
2014-08-08 13:34:24.05 spid51      ODS Event: execrpc : Xact 3300000003 ORS#: 1, connId: 0
2014-08-08 13:34:24.05 spid51      ODS Event: Logout connection 51
2014-08-08 13:34:24.05 spid51      ODS Event: Login connection 51
2014-08-08 13:34:24.05 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:34:24.05 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:34:24.05 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:34:24.05 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc3
2014-08-08 13:34:24.05 spid51          IPC Name: sp_executesql
2014-08-08 13:34:24.05 spid51      ODS Event: execrpc : Xact 3300000003 ORS#: 1, connId: 0
2014-08-08 13:34:24.05 spid51      ODS Event: Logout connection 51
2014-08-08 13:34:24.05 spid51      ODS Event: Login connection 51
2014-08-08 13:34:24.05 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:34:24.05 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:34:24.05 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:34:24.05 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc4
2014-08-08 13:34:24.05 spid51          IPC Name: sp_executesql
2014-08-08 13:34:24.05 spid51      ODS Event: execrpc : Xact 3300000003 ORS#: 1, connId: 0
2014-08-08 13:34:24.05 spid51      ODS Event: Logout connection 51
2014-08-08 13:34:24.05 spid51      ODS Event: Login connection 51
2014-08-08 13:34:24.05 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:34:24.05 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:34:24.05 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:34:24.05 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc5
2014-08-08 13:34:24.05 spid51          IPC Name: sp_executesql
2014-08-08 13:34:24.05 spid51      ODS Event: execrpc : Xact 3300000003 ORS#: 1, connId: 0
2014-08-08 13:34:24.05 spid51      ODS Event: Logout connection 51
2014-08-08 13:34:24.05 spid51      ODS Event: Login connection 51
2014-08-08 13:34:24.05 spid51      ODS Event: Remote_ods : Xact 3300000003 ORS#: 1, connId: 0
2014-08-08 13:34:24.05 spid51      Xact ROLLBACK for Desc: 3300000003
Here is the SQL script when identifier=ID2

Code: Select all

2014-08-08 13:37:29.96 spid51      ODS Event: Remote_ods : Xact 0 ORS#: 1, connId: 0
2014-08-08 13:37:29.96 spid51      Xact BEGIN for Desc: 3300000004
2014-08-08 13:37:29.96 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=948,Len=948,Pxvar Value=SELECT [t1].[Location] AS [Location1]
FROM (
    SELECT [t4].[Location]
    FROM [dbo].[identifiers] [t2]
    INNER JOIN [dbo].[entries] [t3] ON [t2].[IdentifierId] = [t3].[IdentifierId]
    INNER JOIN [dbo].[locations] [t4] ON [t3].[EntryId] = [t4].[EntryId]
    INNER JOIN dbo.[values] [t5] ON [t4].[LocationId] = [t5].[LocationId]
    INNER JOIN [dbo].[types] [t6] ON [t5].[TypeId] = [t6].[TypeId]
    WHERE [t2].[Name] = @p0
    ) [t1]
GROUP BY [t1].[Location]
2014-08-08 13:37:29.96 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=28,Len=28,Pxvar Value=@p0 varchar(3)
2014-08-08 13:37:29.96 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID2
2014-08-08 13:37:29.96 spid51          IPC Name: sp_executesql
2014-08-08 13:37:29.96 spid51      ODS Event: execrpc : Xact 3300000004 ORS#: 1, connId: 0
2014-08-08 13:37:29.96 spid51      ODS Event: Logout connection 51
2014-08-08 13:37:29.96 spid51      ODS Event: Login connection 51
2014-08-08 13:37:29.96 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:37:29.96 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:37:29.96 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:37:29.96 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc1
2014-08-08 13:37:29.96 spid51          IPC Name: sp_executesql
2014-08-08 13:37:29.96 spid51      ODS Event: execrpc : Xact 3300000004 ORS#: 1, connId: 0
2014-08-08 13:37:29.96 spid51      ODS Event: Logout connection 51
2014-08-08 13:37:29.96 spid51      ODS Event: Login connection 51
2014-08-08 13:37:29.96 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:37:29.96 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:37:29.96 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:37:29.96 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc2
2014-08-08 13:37:29.96 spid51          IPC Name: sp_executesql
2014-08-08 13:37:29.96 spid51      ODS Event: execrpc : Xact 3300000004 ORS#: 1, connId: 0
2014-08-08 13:37:29.96 spid51      ODS Event: Logout connection 51
2014-08-08 13:37:29.96 spid51      ODS Event: Login connection 51
2014-08-08 13:37:29.96 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:37:29.96 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:37:29.96 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:37:29.96 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc3
2014-08-08 13:37:29.96 spid51          IPC Name: sp_executesql
2014-08-08 13:37:29.96 spid51      ODS Event: execrpc : Xact 3300000004 ORS#: 1, connId: 0
2014-08-08 13:37:29.96 spid51      ODS Event: Logout connection 51
2014-08-08 13:37:29.96 spid51      ODS Event: Login connection 51
2014-08-08 13:37:29.96 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:37:29.96 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:37:29.96 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:37:29.96 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc4
2014-08-08 13:37:29.96 spid51          IPC Name: sp_executesql
2014-08-08 13:37:29.96 spid51      ODS Event: execrpc : Xact 3300000004 ORS#: 1, connId: 0
2014-08-08 13:37:29.96 spid51      ODS Event: Logout connection 51
2014-08-08 13:37:29.96 spid51      ODS Event: Login connection 51
2014-08-08 13:37:29.96 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:37:29.96 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:37:29.96 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID1
2014-08-08 13:37:29.96 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc5
2014-08-08 13:37:29.96 spid51          IPC Name: sp_executesql
2014-08-08 13:37:29.96 spid51      ODS Event: execrpc : Xact 3300000004 ORS#: 1, connId: 0
2014-08-08 13:37:29.96 spid51      ODS Event: Logout connection 51
2014-08-08 13:37:29.96 spid51      ODS Event: Login connection 51
2014-08-08 13:37:29.96 spid51      ODS Event: Remote_ods : Xact 3300000004 ORS#: 1, connId: 0
2014-08-08 13:37:29.96 spid51      Xact ROLLBACK for Desc: 3300000004
Here is the SQL script when identifier=ID2 (QUERY CACHE IS CLEARED)

Code: Select all

2014-08-08 13:39:08.15 spid51      ODS Event: Remote_ods : Xact 0 ORS#: 1, connId: 0
2014-08-08 13:39:08.15 spid51      Xact BEGIN for Desc: 3300000005
2014-08-08 13:39:08.15 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=948,Len=948,Pxvar Value=SELECT [t1].[Location] AS [Location1]
FROM (
    SELECT [t4].[Location]
    FROM [dbo].[identifiers] [t2]
    INNER JOIN [dbo].[entries] [t3] ON [t2].[IdentifierId] = [t3].[IdentifierId]
    INNER JOIN [dbo].[locations] [t4] ON [t3].[EntryId] = [t4].[EntryId]
    INNER JOIN dbo.[values] [t5] ON [t4].[LocationId] = [t5].[LocationId]
    INNER JOIN [dbo].[types] [t6] ON [t5].[TypeId] = [t6].[TypeId]
    WHERE [t2].[Name] = @p0
    ) [t1]
GROUP BY [t1].[Location]
2014-08-08 13:39:08.15 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=28,Len=28,Pxvar Value=@p0 varchar(3)
2014-08-08 13:39:08.15 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID2
2014-08-08 13:39:08.15 spid51          IPC Name: sp_executesql
2014-08-08 13:39:08.15 spid51      ODS Event: execrpc : Xact 3300000005 ORS#: 1, connId: 0
2014-08-08 13:39:08.15 spid51      ODS Event: Logout connection 51
2014-08-08 13:39:08.15 spid51      ODS Event: Login connection 51
2014-08-08 13:39:08.15 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:39:08.15 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:39:08.15 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID2
2014-08-08 13:39:08.15 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc1
2014-08-08 13:39:08.15 spid51          IPC Name: sp_executesql
2014-08-08 13:39:08.15 spid51      ODS Event: execrpc : Xact 3300000005 ORS#: 1, connId: 0
2014-08-08 13:39:08.15 spid51      ODS Event: Logout connection 51
2014-08-08 13:39:08.15 spid51      ODS Event: Login connection 51
2014-08-08 13:39:08.15 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:39:08.15 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:39:08.15 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID2
2014-08-08 13:39:08.15 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc2
2014-08-08 13:39:08.15 spid51          IPC Name: sp_executesql
2014-08-08 13:39:08.15 spid51      ODS Event: execrpc : Xact 3300000005 ORS#: 1, connId: 0
2014-08-08 13:39:08.15 spid51      ODS Event: Logout connection 51
2014-08-08 13:39:08.15 spid51      ODS Event: Login connection 51
2014-08-08 13:39:08.15 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:39:08.15 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:39:08.15 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID2
2014-08-08 13:39:08.15 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc3
2014-08-08 13:39:08.15 spid51          IPC Name: sp_executesql
2014-08-08 13:39:08.15 spid51      ODS Event: execrpc : Xact 3300000005 ORS#: 1, connId: 0
2014-08-08 13:39:08.15 spid51      ODS Event: Logout connection 51
2014-08-08 13:39:08.15 spid51      ODS Event: Login connection 51
2014-08-08 13:39:08.15 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:39:08.15 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:39:08.15 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID2
2014-08-08 13:39:08.15 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc4
2014-08-08 13:39:08.15 spid51          IPC Name: sp_executesql
2014-08-08 13:39:08.15 spid51      ODS Event: execrpc : Xact 3300000005 ORS#: 1, connId: 0
2014-08-08 13:39:08.15 spid51      ODS Event: Logout connection 51
2014-08-08 13:39:08.15 spid51      ODS Event: Login connection 51
2014-08-08 13:39:08.15 spid51      Parameter# 0: Name=,Flags=0,Xvt=231,MaxLen=988,Len=988,Pxvar Value=SELECT [t5].[Name], [t4].[Value] AS [Value1]
FROM [dbo].[identifiers] [t1]
INNER JOIN [dbo].[entries] [t2] ON [t1].[IdentifierId] = [t2].[IdentifierId]
INNER JOIN [dbo].[locations] [t3] ON [t2].[EntryId] = [t3].[EntryId]
INNER JOIN dbo.[values] [t4] ON [t3].[LocationId] = [t4].[LocationId]
INNER JOIN [dbo].[types] [t5] ON [t4].[TypeId] = [t5].[TypeId]
WHERE (@np0 = [t3].[Location]) AND ([t1].[Name] = @p0)
ORDER BY [t2].[EntryIndex], [t3].[Location], [t5].[Name], [t2].[EntryDateTime]
2014-08-08 13:39:08.15 spid51      Parameter# 1: Name=,Flags=0,Xvt=231,MaxLen=60,Len=60,Pxvar Value=@p0 varchar(3),@np0 varchar(4)
2014-08-08 13:39:08.15 spid51      Parameter# 2: Name=@p0,Flags=0,Xvt=167,MaxLen=3,Len=3,Pxvar Value=ID2
2014-08-08 13:39:08.15 spid51      Parameter# 3: Name=@np0,Flags=0,Xvt=167,MaxLen=4,Len=4,Pxvar Value=Loc5
2014-08-08 13:39:08.15 spid51          IPC Name: sp_executesql
2014-08-08 13:39:08.15 spid51      ODS Event: execrpc : Xact 3300000005 ORS#: 1, connId: 0
2014-08-08 13:39:08.15 spid51      ODS Event: Logout connection 51
2014-08-08 13:39:08.15 spid51      ODS Event: Login connection 51
2014-08-08 13:39:08.15 spid51      ODS Event: Remote_ods : Xact 3300000005 ORS#: 1, connId: 0
2014-08-08 13:39:08.15 spid51      Xact ROLLBACK for Desc: 3300000005
Top
MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Compiled Query Cache Bug

Post by MariiaI » Tue 12 Aug 2014 09:21

Thank you for the additional information. We have contacted you by e-mail.
Top
Action
Posts: 4
Joined: Fri 08 Aug 2014 18:42

Re: Compiled Query Cache Bug

Post by Action » Wed 20 Aug 2014 14:26

Any update on this? I ran into this again on another query.
Top
MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Compiled Query Cache Bug

Post by MariiaI » Thu 21 Aug 2014 06:02

Action wrote:Any update on this? I ran into this again on another query.
Could you please specify the query with which you are encountering this issue.
We are working on it. We will definitely contact you as soon as any results are available.
Top
Action
Posts: 4
Joined: Fri 08 Aug 2014 18:42

Re: Compiled Query Cache Bug

Post by Action » Thu 13 Nov 2014 21:13

It seems this bug always crops up when using a group. I still haven't had any updates on this issue.

Here is another example of a query with the same problem:

Code: Select all

var data = (from go in ctx.Generated_Objects
            join ot in ctx.Object_Types on go.ObjectTypeId equals ot.ObjectTypeId
            where go.Name == Name
            group new { go.Data, go.Name } by ot.Type into g
            select g).ToDictionary(q => q.Key, q => q.Distinct().ToList());
Top
MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Compiled Query Cache Bug

Post by MariiaI » Fri 14 Nov 2014 07:03

Action wrote:It seems this bug always crops up when using a group. I still haven't had any updates on this issue.
Yes, this is a known issue with CompiledQueryCache and queries with GroupBy. The investigation of this behaviour could take some time due to the complexity of the issue. We will inform you about the results as soon as any are available.

As a workaround use one of the following ways:
1. Clear the compiled query cache (like in your sample, which you have sent us earlier by the mail);
2. Disable the compiled query cache. For example, open the properties of the used template "LinqConnect" (select Properties from the template's context menu in the Model Explorer -> Templates), set "Use Compiled Query Cache" to False and save the changes to regenerate the *.Designer.cs file.
Top
Action
Posts: 4
Joined: Fri 08 Aug 2014 18:42

Re: Compiled Query Cache Bug

Post by Action » Fri 11 Sep 2015 20:24

Have they targeted a fix version for this issue yet? Having to clear the cache on some queries has some performance implications.

Thanks,
Top
MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Compiled Query Cache Bug

Post by MariiaI » Tue 15 Sep 2015 05:26

Unfortunately, this issue has not been fixed, yet, due to the some peculiarities and its complexity. We will inform you about the results as soon as any are available.

As a workaround use one of the following ways:
1. Clear the compiled query cache.
2. Disable the compiled query cache. For example, open the properties of the used template "LinqConnect" (select Properties from the template's context menu in the Model Explorer -> Templates), set "Use Compiled Query Cache" to False and save the changes to regenerate the *.Designer.cs file.
Top
Post Reply

Return to “LinqConnect (LINQ to SQL support)”