Hey Everyone,

I've noticed that sometimes when I write a projection query using linq and an iQueryable, if I select a field from a related entity, the query treats that relation as if it was an inner join.

For example, the following query eliminates any records where there does not exist a relationship between the contact and credential tables, as opposed to simply returning null or false.

            var projection = contactQueryable.Select(contact => new
            {
                ColleagueId = contact.ColleagueId,
                Email = contact.Email,
                FirstName = contact.FirstName,
                LastName = contact.LastName,
                IsActive = contact.IsActive,
                HasAccount = contact.Credential.IsApproved // the line in question
            }).ToList();

If I remove the last line in the projection I get all of my records.

Any thoughts as to what I'm doing wrong, or how I could tell llblgen pro to not eliminate those records if the colleague isn't associated with a credential?

Thanks!

It will issue an inner join if the relationship isn't optional, so the FK isn't nullable. If the FK is nullable it will issue a left join (towards the PK side).

Is the FK in Contact for the relationship with Credential nullable? Or is this a 1:1 relationship where Contact is the PK side?

Hey Otis,

Thank you for the quick reply. In our implementation, we have the second option you listed below.

There is a 1:1 relationship between the credential table and the contact table, where contact is the primary key side. In the SQL schema, the credential table has a non-nullable foreign key (contact id) to contact.

In our application, contacts do not have to have an associated credential record. In the Normal Relationship Editor in LLBLGen Pro we have it set to 0..1 Credential is related to 1 Contact.

What I thought this meant, is that if I was querying credential and then joining through to the contact table, then I would have an inner join (because the foreign key field in credential is not nullable). However, If I started at contact and joined through to credential, I would expect an outer join, because contacts are associated with 0..1 credentials.

Is this correct?

Could you please provide the RuntimeLibrary version number exactly as denoted here?

Sure, the version is 3.5.13.429

In v5.3:

[Test]
public void OneToOneInProjectionJoinTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from e in metaData.EmployeePlain
                select new
                       {
                           e.EmployeeId,
                           e.Title,
                           e.SalesPersonPlain.Bonus
                       };
        var results = q.ToList();
        Assert.AreEqual(290, results.Count);
    }
}

where there's a 1:1 PK-PK relationship between employeePlain and SalesPersonPlain (Adventureworks), it works OK

SELECT [LPA_L1].[EmployeeID] AS [EmployeeId],
       [LPA_L1].[Title],
       [LPA_L2].[Bonus]
FROM   ([AdventureWorksUnitTests].[HumanResources].[Employee] [LPA_L1]
        LEFT JOIN [AdventureWorksUnitTests].[Sales].[SalesPerson] [LPA_L2]
            ON [LPA_L1].[EmployeeID] = [LPA_L2].[SalesPersonID]) 

We first fixed this in v5.1.6 (and all later versions have this fix). The only workaround is to explicitly specify the left join with a DefaultIfEmpty() and a join which is more cumbersome to write. That or upgrade to v5.3 (which can't hurt, it's e.g. way faster than 3.5 )

Thanks for the response!

I think we'll probably go down the upgrade path. In terms of pursuing the upgrade, is there documentation available on what goes into upgrading? I would like to have something to present to my project manager if possible, as well as a basis for estimating the time it will take to upgrade.

Thanks again!

Migrating from 3.x should be easier than previous versions, as the same project file format has been used since then.

You need to check the breaking changes listed here: http://llblgen.com/documentation/5.2/Designer/BreakingChanges.htm

Please let us know if you face anything during the migration.

P.S. I trust you will keep a 3.x version of your project file safe, in case you wanted to temporarily rollback to it.