Upgrade to 5.3.2 changes Join generated from Dynamic LINQ

Posts   
 
    
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 20-Apr-2018 23:51:53   

Hello,

My development group has recently made the upgrade from version 3.5.13.429, to version 5.3.2 and have noticed a few errors in our unit tests. Specifically, some of the queries being generated by LLBLGEN pro are now returning different result sets.

Part of our application involves customizable reports where users can specify what columns they wish to have returned. To accomplish this we use an IQueryable and a Dynamic LINQ select to return our results.

For example, an administrator might want to report on dashboards users have created using our in app tools. An example query might be as follows (the expression field typically populated by a user and not just hardcoded):



String expression = "new (Id,Name,CreatedBy.User.FirstName + \" \" + CreatedBy.User.LastName as Creator)"
// code for resolving queryable
IQueryable<dynamic> queryWithSelect = ((IQueryable<dynamic>) queryable.Select(expression)).Distinct();
var results = queryWithSelect.ToArray();
return results;


The above code would return an array of dynamic types with the following fields: Id, Name, Creator.

In case it's important, information about the queried entities are as follows:

  • The queryable above is an instance of IQueryable<DashboardEntity>
  • Id and Name are fields on DashboardEntity
  • FirstName and LastName are fields on UserEntity
  • CreatedBy is a Relationship between the Dashboard and Credential tables where Many Dashboards are related to 0..1 Credentials
  • User is a relationship between Credential and User tables where 0..1 Credentials are related to 1 User

We noticed that after the upgrade, we started getting back a large number of results with many blank fields. We ran sql traces to see what LLBLGEN pro generated and got the following:

Before the update LLBLGEN generated the following sql:


SELECT DISTINCT [LPA_L3].[Id], [LPA_L3].[Name], (([LPA_L4].[FirstName] + ' ') + [LPA_L4].[LastName]) AS [Creator]
FROM (([dbo].[Credential] [LPA_L2]
RIGHT JOIN [dbo].[Dashboard] [LPA_L3] ON [LPA_L2].[CredentialId] = [LPA_L3].[CreatedByCredentialId])
LEFT JOIN [dbo].[User] [LPA_L4] ON [LPA_L4].[UserId] = [LPA_L2].[UserId]) 


Before the update LLBLGEN generated the following sql:


SELECT DISTINCT [LPA_L3].[Id], [LPA_L3].[Name], (([LPA_L4].[FirstName] + ' ') + [LPA_L4].[LastName]) AS [Creator]
FROM (([dbo].[Credential] [LPA_L2]
RIGHT JOIN [dbo].[Dashboard] [LPA_L3] ON [LPA_L2].[CredentialId] = [LPA_L3].[CreatedByCredentialId])
RIGHT JOIN [dbo].[User] [LPA_L4] ON [LPA_L4].[UserId] = [LPA_L2].[UserId]) 


The main difference is that the join to User is now a Right Join instead of a Left Join, so instead of getting a list of all of the dashboards with information on the users who created them, we get that list, plus a list of every user with the dashboard fields null.

We see similar results if we run the query without dynamic link (so we think its probably not due to that):


var results = queryable.Select(x => new
{
        x.Id,
        x.Name,
        Creator = x.CreatedBy.User.FirstName + " " + x.CreatedBy.User.LastName
}).ToArray(); 


One additional thing to include. If I change the relationship between Credential and User to be 0..1 Credentials is related to 0..1 Users and regenerate, the SQL that LLBLGEN Pro creates is the same.

Is there something we need to configure differently in our project or a different way we need to query to prevent this from happening?

Thanks!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Apr-2018 09:40:09   

There was a bugfix some time ago indeed which corrected the way joins were created as the old variant wasn't leading to the correct results in some situations (this is only affecting 1:1 relationships with optional fk sides). Could you give the linq query that created the SQL you posted?

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Apr-2018 10:19:09   

The original issue was reported in a Helpdesk thread, so I'll repost the replies I made there.

(As reply on the original post, with background info why this happens) the right join happens indeed because of the '1:0..1' relationship as the linq code auto joins towards the PK if the FK is nullable if navigators are used (as is the case here, inside the projector), however there might be a mistake in our code here as it doesn't do that on m:1 relationships with a nullable FK, which is basically the same thing. (example: fetch order fields with o.Customer.CompanyName).

Reproduced:


[Test]
public void LinqOneToOneOptionalFkTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        try
        {
            EntityBase2.MarkSavedEntitiesAsFetched = true;
            CustomerEntity newCustomer = EntityCreator.CreateNewCustomer(1);
            newCustomer.TestRunId = _testRunID;
            AddressEntity newAddress = EntityCreator.CreateNewAddress(1);
            newAddress.TestRunId = _testRunID;
            newCustomer.VisitingAddress = newAddress;

            Assert.IsTrue(adapter.SaveEntity(newCustomer));
            // insert dummy address
            AddressEntity dummyAddress = EntityCreator.CreateNewAddress(2);
            dummyAddress.TestRunId = _testRunID;
            adapter.SaveEntity(dummyAddress);

            var metaData = new LinqMetaData(adapter);
            var q = from c in metaData.Customer
                    select new {c.CustomerId, VCountry = c.VisitingAddress.Country, BCountry = c.BillingAddress.Country};
            var results = q.ToList();
            Assert.AreEqual(1, results.Count);
            Assert.AreEqual(newAddress.Country, results[0].VCountry);
            Assert.IsTrue(string.IsNullOrWhiteSpace(results[0].BCountry));
        }
        finally
        {
            EntityBase2.MarkSavedEntitiesAsFetched = false;
        }
    }
}

Query:


SELECT [LPA_L2].[CustomerID] AS [CustomerId],
       [LPA_L1].[Country]   AS [VCountry],
       [LPA_L3].[Country]   AS [BCountry]
FROM   (([LLBLGenProUnitTest].[dbo].[Address] [LPA_L1]
         LEFT JOIN [LLBLGenProUnitTest].[dbo].[Customer] [LPA_L2]
             ON [LPA_L1].[AddressId] = [LPA_L2].[VisitingAddressID])
        LEFT JOIN [LLBLGenProUnitTest].[dbo].[Address] [LPA_L3]
            ON [LPA_L3].[AddressId] = [LPA_L2].[BillingAddressID]) 

2 address rows are inserted, VisitingAddress is a non-nullable 1:1 relationship, BillingAddress is a nullable 1:1 relationships. You see clearly here the code makes a mistake, as it joins towards address, which joins the dummy address I inserted as well. With m:1 it makes the right choice, but with 1:1 it doesn't.

Funny how 10 years in, no-one has ever found this issue in the linq provider! simple_smile (and neither did we!). Looking into why it makes this mistake and how to correct it. (as the query expected here is always joins towards customer, it can't join towards the related element over a navigator. If one wants the behavior that's currently in the code, one should fetch address and navigate to customer, which then would join towards address no matter what, as that's leading).

Frans Bouma | Lead developer LLBLGen Pro
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 23-Apr-2018 17:46:49   

Hey Otis,

Thanks for your reply!

I can reproduce the issue with the following LINQ Query:


var results = queryable.Select(x => new
{
        x.Id,
        x.Name,
        Creator = x.CreatedBy.User.FirstName + " " + x.CreatedBy.User.LastName
}).Distinct().ToArray(); 


The sql this generates is as follows:


SELECT DISTINCT 
     [LPA_L2].[Id], 
     [LPA_L2].[Name], 
     (([LPA_L3].[FirstName] + ' ') + [LPA_L3].[LastName]) AS [Creator]
FROM (([dbo].[Credential] [LPA_L1]
RIGHT JOIN [dbo].[Dashboard] [LPA_L2] ON [LPA_L1].[CredentialId] = [LPA_L2].[CreatedById])
RIGHT JOIN [dbo].[User] [LPA_L3] ON [LPA_L3].[UserId] = [LPA_L1].[UserId])


Relationship Info:

  • Dashboard has a relationship to credential called CreatedBy. The relationship specifics is: Many Dashboard are related to 0..1 Credential
  • Credential has a relationship to User called User. The relationship specifics is: 0..1 Credential are related to 1 User
  • If I change the relationship specifics for the User relationship so that both sides are 0..1, the sql generated is correct
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 23-Apr-2018 19:38:20   

I tried to reproduce it. But, I think we might have implemented the Credential/User relationship differently.

Could you please check the following DDL?

USE [NORTHWIND] GO /****** Object: Table [dbo].[Credential] Script Date: 4/23/2018 10:35:00 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Credential]( [Id] [int] IDENTITY(1,1) NOT NULL, Code nchar NULL, CONSTRAINT [PK_Credential] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO /****** Object: Table [dbo].[Dashboard] Script Date: 4/23/2018 10:35:00 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Dashboard]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NOT NULL, [CreatedBy] [int] NULL, CONSTRAINT [PK_Dashboard] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO /****** Object: Table [dbo].[User] Script Date: 4/23/2018 10:35:00 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[User]( [Id] [int] IDENTITY(1,1) NOT NULL, [FirstName] nchar NULL, [LastName] nchar NULL, [CredID] [int] NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_User] UNIQUE NONCLUSTERED ( [CredID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO ALTER TABLE [dbo].[Dashboard] WITH CHECK ADD CONSTRAINT [FK_Dashboard_Dashboard] FOREIGN KEY([CreatedBy]) REFERENCES [dbo].[Credential] ([Id]) GO ALTER TABLE [dbo].[Dashboard] CHECK CONSTRAINT [FK_Dashboard_Dashboard] GO ALTER TABLE [dbo].[User] WITH CHECK ADD CONSTRAINT [FK_User_Credential] FOREIGN KEY([CredID]) REFERENCES [dbo].[Credential] ([Id]) GO ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Credential] GO

I've used a CredId field on the User Table which has a Unique constraint, and points to the Credential.ID PK.

The output SQL for the same code was: [code]SELECT DISTINCT

[LPA_L2].[Id], [LPA_L2].[Name], (([LPA_L3].[FirstName] + ' ') + [LPA_L3].[LastName]) AS [Creator]

FROM (([NORTHWIND].[dbo].[Credential] [LPA_L1] RIGHT JOIN [NORTHWIND].[dbo].[Dashboard] [LPA_L2] ON [LPA_L1].[Id]=[LPA_L2].[CreatedBy]) RIGHT JOIN [NORTHWIND].[dbo].[User] [LPA_L3] ON [LPA_L1].[Id]=[LPA_L3].[CredID])

mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 23-Apr-2018 23:39:59   

Hey,

I have the DDL below, the main difference is that Credential has the foreign key to the User table on UserId, instead of the other way around.

**[dbo].[User] **


/****** Object:  Table [dbo].[User] Script Date: 4/23/2018 4:22:48 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](100) NOT NULL,
    [LastName] [varchar](100) NOT NULL,
    [Email] [varchar](320) NULL
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO


**[dbo].[Credential] **


/****** Object:  Table [dbo].[Credential]   Script Date: 4/23/2018 4:24:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Credential](
    [CredentialId] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL
 CONSTRAINT [PK_Credential] PRIMARY KEY CLUSTERED 
(
    [CredentialId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [UC_User_Credential_OneToOne] UNIQUE NONCLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO



ALTER TABLE [dbo].[Credential]  WITH NOCHECK ADD  CONSTRAINT [FK_User_Credential_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])

GO

ALTER TABLE [dbo].[Credential] CHECK CONSTRAINT [FK_User_Credential_UserId]
GO


[dbo].[Dashboard]



/****** Object:  Table [dbo].[Dashboard]    Script Date: 4/23/2018 4:26:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Dashboard](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [DashboardTemplateId] [int] NULL,
    [CreatedByCredentialId] [int] NULL,
 CONSTRAINT [PK_Dashboard] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Dashboard]  WITH NOCHECK ADD  CONSTRAINT [FK_Dashboard_Credential_CreatedByCredentialId] FOREIGN KEY([CreatedByCredentialId])
REFERENCES [dbo].[Credential] ([CredentialId])
GO

ALTER TABLE [dbo].[Dashboard] CHECK CONSTRAINT [FK_Dashboard_Credential_CreatedByCredentialId]
GO

Thanks!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Apr-2018 11:56:18   

We'll look into it.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Apr-2018 12:16:58   

Your description:

Credential has a relationship to User called User. The relationship specifics is: 0..1 Credential are related to 1 User

means that credential is optional from the PoV of User, and user is non-optional from the pov of credential. So it can't do anything else but join towards user, hence the right-join towards user. if it would pick any other join it would overrule the fact credential is optional from the pov of User.

In 3.5 it 'worked' because of the bug I posted of above, the fix undid this behavior which you relied on.

Is it the intention to have Credential optional from the pov of User or having user optional from the PoV of credential?

Frans Bouma | Lead developer LLBLGen Pro
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 24-Apr-2018 16:21:29   

You are correct! The credential is optional from the perspective of the user, and the user is non-optional from the perspective of the credential. A credential cannot exist without a user.

Our application typically sits alongside and integrates with some other enterprise application, exposing analytics, reporting, auditing and other functionality over web and service interfaces. The Users table is generally just an integrated copy of whatever list of users exists in the source enterprise application.

When an individual is granted access to log into our app, a credential record is created and tied to their user record. The credential serves to store authentication and similar information, while the user record stores information used for a variety of purposes (authentication, reporting, auditing, etc).

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Apr-2018 16:30:00   

Ok. With that setup, the problem is that you use navigation to create the query. The engine can't do anything else but to use the optional aspect of one side to produce the join. As you have defined Credential to be optional for the user, it will join towards user when a navigation happens, otherwise it might filter out users without a credential. This sounds rather silly perhaps but it's the only way to do this.

There's an easy solution to this though: as User isn't optional from the Credential's side, the FK towards User is not nullable. this means that if you make the relationship in the model 1:1 instead of 0..1:1 nothing will change (no FK field will become non-nullable), other than the flag whether a side is optional in the relationdefinition in the generated code.

If you don't want that, which is understandable as it's not entirely correct, you could reformulate the query to use a join in Linq. This is less ideal, but it's the only way to get the left join you want (or better: the inner join, as a left join towards an optional side is equal to an inner join wink ) so you then have to formulate a FirstOrDefault query on user.

Frans Bouma | Lead developer LLBLGen Pro
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 24-Apr-2018 18:59:26   

Thanks for the suggestions, I will run those by the team and see what they say!

Just an initial question based off of your suggestions. If we set the relationship to be 1:1, will this potentially cause issues in cases where we join to user's who don't have credentials? Some specific cases that come to mind would be:

  • A report that joins to User across a different relationship. Say I want to look at auditing information for changes a user made in the source system, but that user can't log in to our App.
  • A report that joins to User and wants to pull in information off of credential if it exists, but doesn't want to eliminate the record entirely if it doesn't. (like an inner join would do)

In either of the above cases would the query that LLBLGen Pro generates potentially remove records for users without credentials since they don't exist in the system and the 1:1 implies that they do? It seems like it might be a possibility for the first example, and pretty likely for the second.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Apr-2018 22:14:06   

mprothme wrote:

Thanks for the suggestions, I will run those by the team and see what they say!

To be clear, this only is in effect with navigated relationships being converted to joins, so this only happens when you imply a join through navigator traversal in a Linq query. Only then it uses the optional aspect of a relationship as there's no other way to obtain it. In most, if not all other query types, you specify manually what join you want, so it's always explicit.

Just an initial question based off of your suggestions. If we set the relationship to be 1:1, will this potentially cause issues in cases where we join to user's who don't have credentials? Some specific cases that come to mind would be:

  • A report that joins to User across a different relationship. Say I want to look at auditing information for changes a user made in the source system, but that user can't log in to our App.
  • A report that joins to User and wants to pull in information off of credential if it exists, but doesn't want to eliminate the record entirely if it doesn't. (like an inner join would do)

In either of the above cases would the query that LLBLGen Pro generates potentially remove records for users without credentials since they don't exist in the system and the 1:1 implies that they do? It seems like it might be a possibility for the first example, and pretty likely for the second.

Good points, and indeed it will then pick an inner join, and you'll get less data back. So that's not an option....

Leaving it as it is now, however will give you the queries which match what you have defined, let's be clear about that: you define credential to be optional, so it will join towards the non optional side (User) and thus use a right-join. This won't change.

Your points are valid though, and this means you have to define the credential-user join explicitly, so what join to take, which means you have to use a linq query which is more explicit in what you want (namely a normal linq join which will result in an inner join) so you get the SQL you need in this situation (which is a join that doesn't take the default join direction related to the navigated navigator, but one you need for this particular query: an inner join).

Frans Bouma | Lead developer LLBLGen Pro
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 30-May-2018 17:01:15   

Thanks for your reply Otis!

Sorry its taken me so long to get back to you. I was pulled off onto another project and haven't had much time to look at this.

I think explicitly specifying the join direction may be difficult in our reporting tool. Mainly because of how we build the query using dynamic LINQ. Is there potentially a way to specify that the query should just join outward from the entity the queryable is based on?

I guess my initial impression was that when querying using LINQ, the query would start at whatever entity type the queryable was, and then outer join to get the values from the relationship. So if I did something like this:


var queryable = // code to get IQueryable<DashboardEntity>;
var results = queryable.Select(x => new
{
        x.Id,
        x.Name,
        Creator = x.CreatedBy.User.FirstName + " " + x.CreatedBy.User.LastName
}).Distinct().ToArray(); 

I would get SQL that looked like this:


Select Dashboard.Id, 
          Dashboard.Name, 
          User.FirstName + ' ' + User.LastName
From Dashboard 
Left Outer Join Credential on Credential.CredentialId = Dashboard.CreatedById
Left Outer Join User on User.CredentialId = Credential.CredentialId

Evan
User
Posts: 67
Joined: 02-May-2012
# Posted on: 30-May-2018 17:39:28   

Frans, this behavior seems problematic to me in many ways. It seems logical that it would start at the Entity for which the queryable was used and then consider how to join to the other tables. What if there was a 3rd table of the same nature? Will I get the cross product of Table B and Table C when I try to query Table A?

Testing this in LinqPad/Linq2SQL and I get the same behavior as mprothme describes where the query starts at the main entity of the queryable and does two left outer joins.

With SQL out of the picture, is there ever a scenario where the expression inside a select would increase the resultset when evaluated in-process? I can't think of a way that a .Select() would increase or decrease the number of results. There would definitely be no way to even reference these other Users that are being pulled back since they would not be tied to any objects in the queryable.

There are may options when it comes to how to produce the SQL. I feel like this linq and sql(using subqueries as a reasonable alternative to joining) should be logically equivalent and yield similar results:


queryable.Select(x => new {x.Id,x.Name,Creator = x.CreatedBy.User.FirstName + " " + x.CreatedBy.User.LastName})


select d.DashboardId, d.Name, 
(
    select FirstName+' '+LastName from User u where u.UserId = (
        select UserId from Credential c where c.CredentialId = d.CreatedByCredentialId 
    )
) 
from Dashboard d

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 31-May-2018 15:12:47   

Edited, see below

Evan, I don't follow your reasoning, sorry. I also doubt Linq to SQL (but have to test it) creates a different query, simply because it uses the same system we do: IF the FK is optional, it introduces a left join towards the pk. that's exactly what we do too. (see my earlier post in this thread which explains why this is: https://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=140955&ThreadID=24734 and why the old behavior was actually wrong)

So please be specific: what did you try with what build (as that's unclear), and what SQL was generated?

The relationships / joins are in the same order in which they're specified, but sometimes relationships are inserted or appended, depending on the expression tree.

With linq you can't assume anything, as some methods build a different expression tree, and can result in different SQL due to the evaluation of the tree (e.g. nested queries instead of a more flat query).

mprothme wrote:

I think explicitly specifying the join direction may be difficult in our reporting tool. Mainly because of how we build the query using dynamic LINQ. Is there potentially a way to specify that the query should just join outward from the entity the queryable is based on?

That's what it does, actually, as you can see in my posts earlier in this thread. Your problem is with credential and user, both aren't the base of the query (dashboard is). Credential join Dashboard is done with the right join, however user is joined with a right join, which is something you didn't expect and you want it to be a left join. But I can't make it a left join (or inner, same results).

I guess my initial impression was that when querying using LINQ, the query would start at whatever entity type the queryable was, and then outer join to get the values from the relationship. So if I did something like this:


var queryable = // code to get IQueryable<DashboardEntity>;
var results = queryable.Select(x => new
{
        x.Id,
        x.Name,
        Creator = x.CreatedBy.User.FirstName + " " + x.CreatedBy.User.LastName
}).Distinct().ToArray(); 

I would get SQL that looked like this:


Select Dashboard.Id, 
          Dashboard.Name, 
          User.FirstName + ' ' + User.LastName
From Dashboard 
Left Outer Join Credential on Credential.CredentialId = Dashboard.CreatedById
Left Outer Join User on User.CredentialId = Credential.CredentialId

Based on what would there be a left join with User, if I may ask? That's the whole point I think: the bug caused this to happen but this is wrong, User isn't optional from Credential's PoV, it's mandatory (as Credential is optional from User's PoV).

So it uses the correct join between dashboard and credential, and also the correct one (based on the relationship!) between credential and user. It can't do anything else, IMHO, as what would that be? Based on what rule would it do credential left join user ?

I understand you need that SQL to be generated, but the framework can't do that based on what it has and what rules there are defined. I can't find a rule which makes happen what you want, i.e. what criteria to use to generate the SQL you need by specifying navigators and also make sure the query which failed and which lead us to find the original bug still works.

Edit One thing I could think of is if A.B.C.Foo is used, and B is joined optionally (as is the case here), you can't do B right join C as that would kill the results as B is joined with a left join so the rows contains nulls. (this is related to what we've had in the framework for a long time with typed lists and 'weak relationships' which bleed through to other relationships appended to them).

I'll think about this a bit, and see if this is doable with the issue that caused all this to change. I.e. if with this change we can make that work too, then we've a solution (we then still have to implement it wink ).

Frans Bouma | Lead developer LLBLGen Pro
Evan
User
Posts: 67
Joined: 02-May-2012
# Posted on: 31-May-2018 16:31:48   

I tried the scenario discussed in LinqPad v5.26.01 and it produced the same sql I posted in my example:

Select Dashboard.Id, 
         Dashboard.Name, 
         User.FirstName + ' ' + User.LastName
From Dashboard 
Left Outer Join Credential on Credential.CredentialId = Dashboard.CreatedById
Left Outer Join User on User.CredentialId = Credential.CredentialId

I looked for a linq-2-sql version, but didn't find the .dll.

Based on what would there be a left join with User, if I may ask?

This should be based on the expression being evaluated which involves two relationships, not one.

x.CreatedBy.User.FirstName

The fact that User is required for Credential does not negate the fact that Credential is nullable on Dashboard(x). Just like coding in general. Once you hit a null, you're done or once your boolean expression is false you stop evaluating the rest.

Again, this is in a select expression which should not affect the number of results.

So it uses the correct join between dashboard and credential, and also the correct one (based on the relationship!) between credential and user. It can't do anything else, IMHO, as what would that be? Based on what rule would it do credential left join user ?

It should left join based on the fact that the expression tree it's processing is already determined to be optional. A relates to B relates to C. If B is optional for A, but C is required for B, then C is still optional for A. Furthermore it's completely illogical to ask for all A's and get results that only have B's and C's. Honestly, I don't really understand the hang-up on it being required though. If All B's have one and only one C, then an inner join or an outer join from B to C is equivalent.

I feel like there are many ways to skin this cat when it comes to producing SQL. But, however you go about evaluating a select expression, if it produces a different number of results, it's likely missing the mark.

I believe the following would work fine and potentially address your dilemma around how to keep the join logic consistent with how it's working today.


select d.DashboardId, j.FullName
from Dashboard d
left outer join (
    select 
        c.CredentialId,
        FirstName+' '+LastName as FullName
    from Credential c
    inner join User u on u.UserId = c.UserId
) as j on j.CredentialId = d.CreatedByCredentialId

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 31-May-2018 16:53:26   

Evan wrote:

I tried the scenario discussed in LinqPad v5.26.01 and it produced the same sql I posted in my example:

Select Dashboard.Id, 
         Dashboard.Name, 
         User.FirstName + ' ' + User.LastName
From Dashboard 
Left Outer Join Credential on Credential.CredentialId = Dashboard.CreatedById
Left Outer Join User on User.CredentialId = Credential.CredentialId

I looked for a linq-2-sql version, but didn't find the .dll.

Based on what would there be a left join with User, if I may ask?

This should be based on the expression being evaluated which involves two relationships, not one.

x.CreatedBy.User.FirstName

The fact that User is required for Credential does not negate the fact that Credential is nullable on Dashboard(x). Just like coding in general. Once you hit a null, you're done or once your boolean expression is false you stop evaluating the rest.

Sadly you can't compare this to set oriented operations in SQL. It should mimic it, but how to get there isn't as simple as 'it hit null so we can leave it'. That said, see my Edit above what I think can solve this, which comes down to the same result as what you said: credential is joined with optional results, so any join with that can only be a left towards that entity.

Again, this is in a select expression which should not affect the number of results.

You specify navigations which are creating joins to be added before the projection is applied, so a select expression can affect the # of rows on the original source of the projection. Offtopic however wink

So it uses the correct join between dashboard and credential, and also the correct one (based on the relationship!) between credential and user. It can't do anything else, IMHO, as what would that be? Based on what rule would it do credential left join user ?

It should left join based on the fact that the expression tree it's processing is already determined to be optional. A relates to B relates to C. If B is optional for A, but C is required for B, then C is still optional for A. Furthermore it's completely illogical to ask for all A's and get results that only have B's and C's. Honestly, I don't really understand the hang-up on it being required though. If All B's have one and only one C, then an inner join or an outer join from B to C is equivalent.

In this situation it makes sense indeed, however does it make sense in all situations? Mind you, the code we had (and which you relied on) worked for 10 years till someone found a query which made it fail and we had to make the change so that query worked (and yours failed). So it's not as simple as 'this is the situation, this is the rule, it will always work', I wish it was smile . But I'm happy with the idea that if one element in a chain of navigations is joined optionally, all joins should be towards that element (for 1:1 relationships), as that is an extra element we can use to determine what to do in a situation.

I agree with you how it is now isn't sufficient, and if there's a way to determine whether a side of a join is already optional in the resultset, we can take that into account (it currently doesn't do that). We'll try to alter the fix we have with this to see if this satisfies the query that brought the bug to light and yours.

Note: things aren't simply stored in neat tiny elements in an expression tree however so this is more hairy than it looks (so it's sadly not as simple as 'simply stop processing the tree', but as this is a chain of navigations it isn't extremely complex as all elements are close together (compared to e.g. a DefaultIfEmpty() call which can be on an element tucked away in a temp projection).

Frans Bouma | Lead developer LLBLGen Pro
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 31-May-2018 16:58:26   

Hey Otis,

Thanks again for your reply!

Otis wrote:

That's what it does, actually, as you can see in my posts earlier in this thread.

I think there may be some confusion. My thought would be that the joins would happen outward from Dashboard. In the query that's generated it seems like Credential is the first table in the join, and then the query right joins to Dashboard and then to User.

SELECT DISTINCT 
     [LPA_L2].[Id], 
     [LPA_L2].[Name], 
     (([LPA_L3].[FirstName] + ' ') + [LPA_L3].[LastName]) AS [Creator]
FROM (([dbo].[Credential] [LPA_L1]
RIGHT JOIN [dbo].[Dashboard] [LPA_L2] ON [LPA_L1].[CredentialId] = [LPA_L2].[CreatedById])
RIGHT JOIN [dbo].[User] [LPA_L3] ON [LPA_L3].[UserId] = [LPA_L1].[UserId])

Otis wrote:

Credential join Dashboard is done with the right join, however user is joined with a right join, which is something you didn't expect and you want it to be a left join.

I was actually expecting something like what I mentioned earlier (one base query with outer joins), and I guess in the past because the generated query behaved as expected we never delved into the sql that was being generated.

Otis wrote:

Based on what would there be a left join with User, if I may ask?

The left join to user is just based on the fact that, in the LINQ at least, the base of my select is the dashboard, and I'm trying to pull data outward from that entity. My thinking is just based on how I would expect the LINQ to perform if it wasn't being turned into sql. If I had the full entity tree (dashboard, credential, user) fetched into memory and did a select off of a list of dashboard items, where the result was the projection I posted earlier, I would expect to have one result for each dashboard item, and potentially either a string value for Creator, or null (if any part of the x.CreatedBy.User path was null).

Otis wrote:

Edit One thing I could think of is if A.B.C.Foo is used, and B is joined optionally (as is the case here), you can't do B right join C as that would kill the results as B is joined with a left join so the rows contains nulls.

If I'm correctly understanding what you're saying, that may be whats happening. Instead of the query returning one row for each dashboard record (with the created fields filled in), we're getting on row for every dashboard with the fields filled in as well as one record for every user with the dashboard fields (name, id) set to null.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 31-May-2018 19:20:12   

I think we're all in agreement now simple_smile The current fix isn't sufficient as there's an additional check possible, namely if you have A.B.C.Foo, you can check for the join B.C if B is joined using a join which could lead to optional values for B (e.g. A left join B), in which case B join C would need to preserve that so it has to take that into account, it currently doesn't.

This might be complicated to achieve, but OTOH, it's a sequence of navigations so it's close together in the tree and processed in 1 pass so the code itself shouldn't be that complicated. I hope if that change is added the original query causing the problem still works and also yours, we'll see simple_smile I hope this fix can be merged tomorrow, otherwise it'll be Monday

Frans Bouma | Lead developer LLBLGen Pro
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 31-May-2018 19:34:47   

Thanks for all of your help!

I hope this fix can be merged tomorrow, otherwise it'll be Monday

and your turn around time is ridiculous (ridiculously good simple_smile ).

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 01-Jun-2018 10:05:03   

mprothme wrote:

Thanks for all of your help!

I hope this fix can be merged tomorrow, otherwise it'll be Monday

and your turn around time is ridiculous (ridiculously good simple_smile ).

smile

Even better, this has already been fixed, in v5.3.5 simple_smile

Linq: a navigation over a 1:1 relationship in a projection could lead to a join towards the related element causing an invalid resultset

Test: (data is setup in such a way that it should lead to 4 rows with the query you want and 0 with the query it did)


    using(var adapter = new DataAccessAdapter())
    {
        // this uses a 3 hop navigation, which will should lead to 4 rows, due to special casing the optionality of the middle entity.
        var metaData = new LinqMetaData(adapter);
        var q = from d in metaData.Dashboard
                select new
                       {
                           d.Id,
                           d.Name,
                           Creator = d.Credential.User.FirstName + " " + d.Credential.User.LastName
                       };
        var results = q.ToList();
        Assert.AreEqual(4, results.Count);
    }

SQL:


SELECT [LPA_L2].[Id],
       [LPA_L2].[Name],
       (([LPA_L3].[FirstName] + ' ' /* @p2 */) + [LPA_L3].[LastName]) AS [Creator]
FROM   (([LLBLGenProUnitTest].[dbo].[Credential] [LPA_L1]
         RIGHT JOIN [LLBLGenProUnitTest].[dbo].[Dashboard] [LPA_L2]
             ON [LPA_L1].[CredentialId] = [LPA_L2].[CreatedByCredentialId])
        LEFT JOIN [LLBLGenProUnitTest].[dbo].[User] [LPA_L3]
            ON [LPA_L3].[Id] = [LPA_L1].[UserId])

5.3.5 is available on the website under 'my account'.

Frans Bouma | Lead developer LLBLGen Pro
Evan
User
Posts: 67
Joined: 02-May-2012
# Posted on: 01-Jun-2018 17:08:36   

Excellent! Thanks Frans, we really appreciate talking through it with us and working toward a solution. I'll pull that version into LinqPad to check it out.

I am still mildly curious when/why it does a right join to the origin rather than a left join to the destination.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 04-Jun-2018 11:20:20   

Our join system uses Pkside join Fkside for all joins, so if you specify Fkside left join Pkside, then it'll make it Pkside right join Fkside.

This is to make things consistent and make sure the intent of a query produces the same SQL, which helps with re-using query plans on the server as the SQL is the same. So if you first specify A left join B and then later on B right join A, they'll result in the same SQL.

Frans Bouma | Lead developer LLBLGen Pro