Related Projection problem.

Posts   
 
    
Brandt
User
Posts: 142
Joined: 04-Apr-2007
# Posted on: 15-Jan-2009 20:29:14   

Can you tell my why the first query works and the second one doesn't. The first query correctly issues two sql calls. The second issues a subselect.

LINQ Support Classes Lib - 2.6.8.1219 ORM Support Classes Lib - 2.6.8.1211



        [TestMethod()]
        public void TestBasicLinqQueryWithProjectionAndRelatedEntitiesWithProjection2()
        {
            var qry = (from l in context.CVE
                        select new
                        {
                            l.CVEId,
                            Comments =  l.CVEComment.Select(a=> new { a.CVECommentId, a.CreatedOn })
                        });
            var list = qry.ToList();


            Assert.IsTrue(list != null);
        }


SQL Issued :
exec sp_executesql N'SELECT [LPLA_1].[CVEId], [LPLA_1].[CVENumber], @LO11 AS [LPFA_2] FROM [VRRA].[Vulnerability].[CVE] [LPLA_1] ',N'@LO11 int',@LO11=1

SELECT [LPLA_2].[CVECommentId], [LPLA_2].[CreatedOn], [LPLA_2].[CVEId] FROM [VRRA].[Vulnerability].[CVEComment] [LPLA_2]  WHERE ( ( (  EXISTS (SELECT [LPLA_1].[CVEId] FROM [VRRA].[Vulnerability].[CVE] [LPLA_1]  WHERE ( [LPLA_1].[CVEId] = [LPLA_2].[CVEId])))))

This one throws an exception.


        [TestMethod()]
        public void TestBasicLinqQueryWithProjectionAndRelatedEntitiesWithProjection2()
        {
            var qry = (from l in context.CVE
                        select new
                        {
                            l.CVEId,
                            Comments =  l.CVEComment.Select(a=> new { a.CVECommentId })
                        });
            var list = qry.ToList();


            Assert.IsTrue(list != null);
        }



Test method VRRA.Tests.LLBLGenTests.TestBasicLinqQueryWithProjectionAndRelatedEntitiesWithProjection2 threw exception:  System.Data.SqlClient.SqlException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression..


SQL Issued :
 SELECT [LPLA_1].[CVEId], [LPLA_1].[CVENumber], (SELECT [LPLA_2].[CVECommentId] FROM [VRRA].[Vulnerability].[CVEComment] [LPLA_2]  WHERE ( ( [LPLA_1].[CVEId] = [LPLA_2].[CVEId]))) AS [LPFA_2] FROM [VRRA].[Vulnerability].[CVE] [LPLA_1] 



Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-Jan-2009 21:07:22   

Hmm. the subquery shouldn't be converted to a scalar query as it returns an IEnumerable/IQueryable... Will check it out.

Frans Bouma | Lead developer LLBLGen Pro
Brandt
User
Posts: 142
Joined: 04-Apr-2007
# Posted on: 15-Jan-2009 21:24:14   

Otis wrote:

Hmm. the subquery shouldn't be converted to a scalar query as it returns an IEnumerable/IQueryable... Will check it out.

Thanks.

On an aside note. Any time I try to use WithPath Visual Studio crashes. I have to comment out the using line to the LINQ Support dll and type in the code and then uncomment it. VS then crashes but when i bring it back up it will work unless i try to mess with the WithPath again. Do you know what might be causing that.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-Jan-2009 21:44:00   

No I'm sorry I have no idea. It never crashed on me working with linq code. Did you install sp1? Or do you have some beta code installed perhaps?

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Jan-2009 10:07:23   

Reproduced

(edit) Fixed in next build. There's another linq bug pending (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=15100 ) so I'll attach a new build to that thread, if required (if not, I'll attach a new build here)

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Jan-2009 11:43:36   

See this post for a build which contains the fix (and the fix of the thread the post is in wink ). http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=84068&ThreadID=15100

Frans Bouma | Lead developer LLBLGen Pro