[
https://issues.apache.org/jira/browse/IGNITE-4415?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15748513#comment-15748513
]
Dmitry Zhukov commented on IGNITE-4415:
---------------------------------------
I investigated the problem and found that even the first query fails
{code} var regionsAsm = user.GetAllChildRegions()
.Where(w => w.RegionToRole.Any(a => a.RoleID ==
(int)UserRoles.ASM));
{code}
{{user.GetAllChildRegions()}} does not fail and adding {{Where(...)}} condition
causes problems.
{{user.GetAllChildRegions()}} is translated into
{code}
SELECT
[Extent5].[ID] AS [ID],
[Extent5].[ParentID] AS [ParentID],
[Extent5].[Name] AS [Name],
[Extent5].[CreationDate] AS [CreationDate],
[Extent5].[IsDeleted] AS [IsDeleted]
FROM [dbo].[User] AS [Extent1]
INNER JOIN (SELECT [Extent2].[UserID] AS [UserID], [Extent3].[RegionID] AS
[RegionID]
FROM [dbo].[UserToRegionRole] AS [Extent2]
INNER JOIN [dbo].[RegionToRole] AS [Extent3] ON
[Extent2].[RegionRoleID] = [Extent3].[ID] ) AS [Join1] ON [Extent1].[ID] =
[Join1].[UserID]
INNER JOIN [dbo].[RegionExpand] AS [Extent4] ON [Join1].[RegionID] =
[Extent4].[ParentRegionID]
INNER JOIN [dbo].[Region] AS [Extent5] ON [Extent4].[RegionID] =
[Extent5].[ID]
WHERE ([Extent1].[IsDeleted] <> 1) AND ([Extent1].[ID] = @p__linq__0)
{code}
Failing query is translated into
{code}
SELECT
[Project1].[ID] AS [ID],
[Project1].[ParentID] AS [ParentID],
[Project1].[Name] AS [Name],
[Project1].[CreationDate] AS [CreationDate],
[Project1].[IsDeleted] AS [IsDeleted]
FROM ( SELECT
[Extent5].[ID] AS [ID],
[Extent5].[ParentID] AS [ParentID],
[Extent5].[Name] AS [Name],
[Extent5].[CreationDate] AS [CreationDate],
[Extent5].[IsDeleted] AS [IsDeleted]
FROM [dbo].[User] AS [Extent1]
INNER JOIN (SELECT [Extent2].[UserID] AS [UserID],
[Extent3].[RegionID] AS [RegionID]
FROM [dbo].[UserToRegionRole] AS [Extent2]
INNER JOIN [dbo].[RegionToRole] AS [Extent3] ON
[Extent2].[RegionRoleID] = [Extent3].[ID] ) AS [Join1] ON [Extent1].[ID] =
[Join1].[UserID]
INNER JOIN [dbo].[RegionExpand] AS [Extent4] ON [Join1].[RegionID] =
[Extent4].[ParentRegionID]
INNER JOIN [dbo].[Region] AS [Extent5] ON [Extent4].[RegionID] =
[Extent5].[ID]
WHERE ([Extent1].[IsDeleted] <> 1) AND ([Extent1].[ID] = @p__linq__0)
) AS [Project1]
WHERE EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RegionToRole] AS [Extent6]
WHERE ([Project1].[ID] = [Extent6].[RegionID]) AND (7 =
[Extent6].[RoleID])
)
{code}
> .NET: EntityFramework 2nd level cache fails for some complex queries
> --------------------------------------------------------------------
>
> Key: IGNITE-4415
> URL: https://issues.apache.org/jira/browse/IGNITE-4415
> Project: Ignite
> Issue Type: Bug
> Components: platforms
> Affects Versions: 1.8
> Reporter: Pavel Tupitsyn
> Assignee: Pavel Tupitsyn
> Labels: .NET
> Fix For: 2.0
>
>
> This EF query (sent by user) fails when EF caching is enabled:
> {code}
> var regionsAsm = user.GetAllChildRegions()
> .Where(w => w.RegionToRole.Any(a => a.RoleID ==
> (int)UserRoles.ASM));
>
> var allInvestTitle = user.RepositoryFactory.InvestTitle.All();
>
> var isFutureYear = filters.ActualYear.Value.Year >
>
> user.RepositoryFactory.Version.All().SingleOrDefault(w =>
> w.IsCurrent)?.ActualYear;
>
> var distributorToRegionAsmBindings = !isFutureYear
> ? regionsAsm.SelectMany(s => s.DistributorToRegionAsmBinding)
> : regionsAsm.SelectMany(s =>
> s.DistributorToRegionAsmBinding.Where(b => b.IsActual));
>
>
> var resultAllBudgets = distributorToRegionAsmBindings
> .Select(d => new { Distributor = d.Distributor, RegionAsm =
> d.Region, d })
> .SelectMany(dr => allInvestTitle, (dr, t) => new {
> dr.Distributor, dr.RegionAsm, InvestTitle = t, dr.d });
>
> var allBudgets = resultAllBudgets
> .Select(b => new BudgetResult
> {
> AsmRegionId = b.RegionAsm.ID,
> AsmRegionName = b.RegionAsm.Name,
> DistributorId = b.Distributor.ID,
> DistributorName = b.Distributor.Name,
> RsmRegionId = b.RegionAsm.RegionExpand
> .Where(w => w.Region1.RegionToRole.Any(a =>
> a.RoleID == (int) UserRoles.RSM))
> .Select(ss => ss.ParentRegionID)
> .FirstOrDefault(),
> RsmRegionName = b.RegionAsm.RegionExpand
> .Where(w => w.Region1.RegionToRole.Any(a =>
> a.RoleID == (int) UserRoles.RSM))
> .Select(ss => ss.Region1.Name)
> .FirstOrDefault(),
> InvestTitleId = b.InvestTitle.ID,
> InvestTitleName = b.InvestTitle.Name,
> });
>
> var result = allBudgets.ToList();
> {code}
> Error:
> {code}
> class org.apache.ignite.binary.BinaryObjectException: Unexpected flag value
> [pos=43775, expected=12, actual=0]
> at
> org.apache.ignite.internal.binary.BinaryReaderExImpl.checkFlag(BinaryReaderExImpl.java:1403)
> at
> org.apache.ignite.internal.binary.BinaryReaderExImpl.readByteArray(BinaryReaderExImpl.java:468)
> at
> org.apache.ignite.internal.processors.platform.entityframework.PlatformDotNetEntityFrameworkCacheExtension.processInOutStreamLong(PlatformDotNetEntityFrameworkCacheExtension.java:125)
> at
> org.apache.ignite.internal.processors.platform.cache.PlatformCache.processInStreamOutLong(PlatformCache.java:761)
> at
> org.apache.ignite.internal.processors.platform.PlatformAbstractTarget.inStreamOutLong(PlatformAbstractTarget.java:78)
> {code}
> Generated SQL:
> {code}
> SELECT
> [Project17].[ID2] AS [ID],
> [Project17].[ID1] AS [ID1],
> [Project17].[Name1] AS [Name],
> [Project17].[ID] AS [ID2],
> [Project17].[Name] AS [Name1],
> CASE WHEN ([Project17].[C1] IS NULL) THEN 0 ELSE [Project17].[C2] END AS
> [C1],
> [Project17].[C3] AS [C2],
> [Project17].[Name2] AS [Name2]
> FROM ( SELECT
> [Project13].[ID] AS [ID],
> [Project13].[Name] AS [Name],
> [Project13].[ID1] AS [ID1],
> [Project13].[Name1] AS [Name1],
> [Project13].[ID2] AS [ID2],
> [Project13].[Name2] AS [Name2],
> [Project13].[C1] AS [C1],
> [Project13].[C2] AS [C2],
> (SELECT TOP (1)
> [Project14].[Name] AS [Name]
> FROM ( SELECT
> [Extent14].[ParentRegionID] AS [ParentRegionID],
> [Extent15].[ID] AS [ID],
> [Extent15].[Name] AS [Name]
> FROM [dbo].[RegionExpand] AS [Extent14]
> INNER JOIN [dbo].[Region] AS [Extent15] ON
> [Extent14].[ParentRegionID] = [Extent15].[ID]
> WHERE [Project13].[ID1] = [Extent14].[RegionID]
> ) AS [Project14]
> WHERE EXISTS (SELECT
> 1 AS [C1]
> FROM [dbo].[RegionToRole] AS [Extent16]
> WHERE ([Project14].[ParentRegionID] = [Extent16].[RegionID])
> AND (5 = [Extent16].[RoleID])
> )) AS [C3]
> FROM ( SELECT
> [Project12].[ID] AS [ID],
> [Project12].[Name] AS [Name],
> [Project12].[ID1] AS [ID1],
> [Project12].[Name1] AS [Name1],
> [Project12].[ID2] AS [ID2],
> [Project12].[Name2] AS [Name2],
> [Project12].[C1] AS [C1],
> [Project12].[C2] AS [C2]
> FROM ( SELECT
> [Project8].[ID] AS [ID],
> [Project8].[Name] AS [Name],
> [Project8].[ID1] AS [ID1],
> [Project8].[Name1] AS [Name1],
> [Project8].[ID2] AS [ID2],
> [Project8].[Name2] AS [Name2],
> [Project8].[C1] AS [C1],
> (SELECT TOP (1)
> [Project9].[ParentRegionID] AS [ParentRegionID]
> FROM ( SELECT
> [Extent12].[ParentRegionID] AS [ParentRegionID]
> FROM [dbo].[RegionExpand] AS [Extent12]
> WHERE [Project8].[ID1] = [Extent12].[RegionID]
> ) AS [Project9]
> WHERE EXISTS (SELECT
> 1 AS [C1]
> FROM [dbo].[RegionToRole] AS [Extent13]
> WHERE ([Project9].[ParentRegionID] =
> [Extent13].[RegionID]) AND (5 = [Extent13].[RoleID])
> )) AS [C2]
> FROM ( SELECT
> [Project7].[ID] AS [ID],
> [Project7].[Name] AS [Name],
> [Project7].[ID1] AS [ID1],
> [Project7].[Name1] AS [Name1],
> [Project7].[ID2] AS [ID2],
> [Project7].[Name2] AS [Name2],
> [Project7].[C1] AS [C1]
> FROM ( SELECT
> [Project3].[ID] AS [ID],
> [Project3].[Name] AS [Name],
> [Project3].[ID1] AS [ID1],
> [Project3].[Name1] AS [Name1],
> [Project3].[ID2] AS [ID2],
> [Project3].[Name2] AS [Name2],
> (SELECT TOP (1)
> [Project4].[ParentRegionID] AS [ParentRegionID]
> FROM ( SELECT
> [Extent10].[ParentRegionID] AS
> [ParentRegionID]
> FROM [dbo].[RegionExpand] AS [Extent10]
> WHERE [Project3].[ID1] = [Extent10].[RegionID]
> ) AS [Project4]
> WHERE EXISTS (SELECT
> 1 AS [C1]
> FROM [dbo].[RegionToRole] AS [Extent11]
> WHERE ([Project4].[ParentRegionID] =
> [Extent11].[RegionID]) AND (5 = [Extent11].[RoleID])
> )) AS [C1]
> FROM ( SELECT
> [Project1].[ID] AS [ID],
> [Project1].[Name] AS [Name],
> [Project1].[ID1] AS [ID1],
> [Project1].[Name1] AS [Name1],
> [Project1].[ID2] AS [ID2],
> [Project1].[Name2] AS [Name2]
> FROM ( SELECT
> [Extent4].[RegionID] AS [RegionID],
> [Extent5].[DistributorID] AS [DistributorID],
> [Extent5].[RegionAsmID] AS [RegionAsmID],
> [Extent6].[ID] AS [ID],
> [Extent6].[Name] AS [Name],
> [Extent7].[ID] AS [ID1],
> [Extent7].[Name] AS [Name1],
> [Extent8].[ID] AS [ID2],
> [Extent8].[Name] AS [Name2]
> FROM [dbo].[User] AS [Extent1]
> INNER JOIN (SELECT [Extent2].[UserID] AS
> [UserID], [Extent3].[RegionID] AS [RegionID]
> FROM [dbo].[UserToRegionRole] AS
> [Extent2]
> INNER JOIN [dbo].[RegionToRole] AS
> [Extent3] ON [Extent2].[RegionRoleID] = [Extent3].[ID] ) AS [Join1] ON
> [Extent1].[ID] = [Join1].[UserID]
> INNER JOIN [dbo].[RegionExpand] AS [Extent4]
> ON [Join1].[RegionID] = [Extent4].[ParentRegionID]
> INNER JOIN
> [dbo].[DistributorToRegionAsmBinding] AS [Extent5] ON [Extent4].[RegionID] =
> [Extent5].[RegionAsmID]
> INNER JOIN [dbo].[Distributor] AS [Extent6]
> ON [Extent5].[DistributorID] = [Extent6].[ID]
> INNER JOIN [dbo].[Region] AS [Extent7] ON
> [Extent5].[RegionAsmID] = [Extent7].[ID]
> CROSS JOIN [dbo].[InvestTitle] AS [Extent8]
> WHERE ([Extent1].[IsDeleted] <> 1) AND
> ([Extent1].[ID] = @p__linq__0)
> ) AS [Project1]
> WHERE EXISTS (SELECT
> 1 AS [C1]
> FROM [dbo].[RegionToRole] AS [Extent9]
> WHERE ([Project1].[RegionID] =
> [Extent9].[RegionID]) AND (7 = [Extent9].[RoleID])
> )
> ) AS [Project3]
> ) AS [Project7]
> ) AS [Project8]
> ) AS [Project12]
> ) AS [Project13]
> ) AS [Project17]
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
