Is the following not supported with Linq 2 LLBL? Note the left outer join into tempAddress BUT in the projection selection, the address table is joined using an INNER JOIN. Seems that other providers can infer this, not a big deal, just wanted to know for furture reference.

    var q = from u in UserProfile 
        join ua in User_Address on u.UserID equals ua.UserID into tempAddress
        from ad in tempAddress.DefaultIfEmpty()
        select new 
        {
            UniqueId = u.UniqueID,
            Address1 = ad.Address.Address1,
            Address2 = ad.Address.Address2,
            City = ad.Address.City,
            State = ad.Address.StateCode,
            Zip = ad.Address.PostalCode,
        };

I noticed it works as expected in Linq 2 SQL but just wanted to check.

What results to you actually get with Linq2LLBLGen...?

Matt

MTrinder wrote:

What results to you actually get with Linq2LLBLGen...?

Matt

The sql generated is an INNER JOIN from user_address to address.

I'll ask the dev team to take a look at it for you.

Matt

MTrinder wrote:

I'll ask the dev team to take a look at it for you.

Matt

thanks

1) runtime build nr ? 2) what are 'UserProfile' and 'User_Address' ?

Otis wrote:

1) runtime build nr ? 2) what are 'UserProfile' and 'User_Address' ?

Sorry, I should have known better.

1) v2.0.50727 2) UserProfile is just our user table and User_Address is our table that allows users to have more than one address. So, it contains two foreign keys, userId and addressId

MarcoP wrote:

Otis wrote:

1) runtime build nr ? 2) what are 'UserProfile' and 'User_Address' ?

Sorry, I should have known better.

1) v2.0.50727

That's the .net version right-click the ormsupportclasses dll in windows explorer -> properties -> version tab. Also please the linq provider dll version.

2) UserProfile is just our user table and User_Address is our table that allows users to have more than one address. So, it contains two foreign keys, userId and addressId

Yes, but you didn't use LinqMetaData.<entityname> so this is unclear to me

Otis wrote:

MarcoP wrote:

Otis wrote:

1) runtime build nr ? 2) what are 'UserProfile' and 'User_Address' ?

Sorry, I should have known better.

1) v2.0.50727

That's the .net version right-click the ormsupportclasses dll in windows explorer -> properties -> version tab. Also please the linq provider dll version.

2) UserProfile is just our user table and User_Address is our table that allows users to have more than one address. So, it contains two foreign keys, userId and addressId

Yes, but you didn't use LinqMetaData.<entityname> so this is unclear to me

hehe...geesh its early!

Here you go:

Versions: 2.6.10.0421 2.6.10.0315 (LINQ)

Sorry about that, I just copied that from LinqPad. Here is my actual code:

            var q = from o in MetaData.Order
                    join oi in MetaData.Order_Item on o.OrderID equals oi.OrderID
                    join i in MetaData.Item on oi.ItemID equals i.ItemID
                    join ua in MetaData.User_Address on o.UserID equals ua.UserID into tempUa
                    from address in tempUa.DefaultIfEmpty()
                    join up in MetaData.User_Phone on o.UserID equals up.UserID into tempUp
                    from phone in tempUp.DefaultIfEmpty()
                    where o.Active == true
                    where address.Active == true
                    where phone.Active == true
                    select new OrderDTO
                    {
                        OrderId = o.OrderID,
                        OrderDate = o.CreatedOn,
                        FirstName = o.UserProfile.FirstName,
                        LastName = o.UserProfile.LastName,
                        BannerId = o.UserProfile.UniqueID,
                        Address1 = address.Address.Address1,
                        Address2 = address.Address.Address2,
                        City = address.Address.City,
                        State = address.Address.StateCode,
                        ZipCode = address.Address.PostalCode,
                        PhoneNumber = phone.Phone.FullNumber,
                        ItemCode = i.UniqueID,
                        ItemDescription = i.ItemNm,
                        Quantity = oi.Quantity,
                        TotalPoints = oi.TotalPoints.GetValueOrDefault(),
                        Status = o.OrderStatus.OrderStatusNm
                    };

Please try the latest version of the runtime libraries.

The following code:

var q = from c in metaData.Customers
                    join o in metaData.Orders on c.CustomerId equals o.CustomerId into co
                    from o in co.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        c.City,
                        c.Country,
                        o.OrderDate,
                        o.Freight
                    };

Produces the following SQL:

SELECT 
[LPA_L1].[CompanyName], 
[LPA_L1].[City], 
[LPA_L1].[Country], 
[LPA_L2].[OrderDate], 
[LPA_L2].[Freight] 
FROM 
( [Northwind].[dbo].[Customers] [LPA_L1]  
LEFT JOIN 
[Northwind].[dbo].[Orders] [LPA_L2]  
ON  [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID])

Walaa wrote:

Please try the latest version of the runtime libraries.

The following code:

var q = from c in metaData.Customers
                    join o in metaData.Orders on c.CustomerId equals o.CustomerId into co
                    from o in co.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        c.City,
                        c.Country,
                        o.OrderDate,
                        o.Freight
                    };

Produces the following SQL:

SELECT 
[LPA_L1].[CompanyName], 
[LPA_L1].[City], 
[LPA_L1].[Country], 
[LPA_L2].[OrderDate], 
[LPA_L2].[Freight] 
FROM 
( [Northwind].[dbo].[Customers] [LPA_L1]  
LEFT JOIN 
[Northwind].[dbo].[Orders] [LPA_L2]  
ON  [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID])

Yes, but you are missing the following in your projections to do an implicit join:

c.SomeOtherRelatedEntity.Property

OK, the following code.

            var q = from o in metaData.Orders
                    join od in metaData.OrderDetails on o.OrderId equals od.OrderId into oz
                    from od in oz.DefaultIfEmpty()
                    select new
                               {
                                   o.OrderId,
                                   o.OrderDate,
                                   od.Quantity,
                                   od.UnitPrice,
                                   o.Customers.ContactName
                               };

Produces the following SQL.

SELECT 
[LPA_L1].[OrderID] AS [OrderId], 
[LPA_L1].[OrderDate], 
[LPA_L2].[Quantity], 
[LPA_L2].[UnitPrice], 
[LPA_L3].[ContactName] 
FROM 
(
( [Northwind].[dbo].[Orders] [LPA_L1]  
LEFT JOIN [Northwind].[dbo].[Order Details] [LPA_L2]  ON  [LPA_L1].[OrderID] = [LPA_L2].[OrderID]) 
LEFT JOIN [Northwind].[dbo].[Customers] [LPA_L3]  ON  [LPA_L3].[CustomerID]=[LPA_L1].[CustomerID]
)

Walaa wrote:

OK, the following code.

            var q = from o in metaData.Orders
                    join od in metaData.OrderDetails on o.OrderId equals od.OrderId into oz
                    from od in oz.DefaultIfEmpty()
                    select new
                               {
                                   o.OrderId,
                                   o.OrderDate,
                                   od.Quantity,
                                   od.UnitPrice,
                                   o.Customers.ContactName
                               };

Produces the following SQL.

SELECT 
[LPA_L1].[OrderID] AS [OrderId], 
[LPA_L1].[OrderDate], 
[LPA_L2].[Quantity], 
[LPA_L2].[UnitPrice], 
[LPA_L3].[ContactName] 
FROM 
(
( [Northwind].[dbo].[Orders] [LPA_L1]  
LEFT JOIN [Northwind].[dbo].[Order Details] [LPA_L2]  ON  [LPA_L1].[OrderID] = [LPA_L2].[OrderID]) 
LEFT JOIN [Northwind].[dbo].[Customers] [LPA_L3]  ON  [LPA_L3].[CustomerID]=[LPA_L1].[CustomerID]
)

Ok, I'll try updating my version. Thanks for looking into this.

I just noticed something. The difference again is in the projection you need to get the join off the initial table you are left joining with. So, you need something joining off of 'oz'.

I can't reproduce it. Maybe I'm missing something, would you please modify my code the way you like, post it here and let me try it out.

btw, Have you tried the latest version?