linq to sql generated query

[lnu]

Hello,

i've been playing with dotconnect for Oracle for two days and have found strange things.

Here is my setup:

- one Sql 2005 Server Express with Northwind
- one Oracle XE with Northwind script adapted for Oracle
- One Linq To Sql model for sql 2005 made with VS and one for Oracle made with the Entity Developer

The two model are nearly identical.

If I run these linq queries:

Code: Select all

 
var resNorthwindSql = from p in contextNorthwindSql.Products 
select new { p.Category.CategoryName, p.ProductName, p.Supplier.CompanyName,p.Supplier.ContactName };

Code: Select all

var resNorthwindOracle = from p in contextNorthwindOracle.Products
select new { p.Category.Categoryname, p.Productname, p.Supplier.Companyname, p.Supplier.Contactname };

I got two different generated sql statement:

Microsoft Linq

Code: Select all

SELECT [t1].[CategoryName], [t0].[ProductName], [t2].[CompanyName], [t2].[Contac
tName]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t1].[CategoryID] = [t0].[Category
ID]
LEFT OUTER JOIN [dbo].[Suppliers] AS [t2] ON [t2].[SupplierID] = [t0].[SupplierI
D]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Devart Linq

Code: Select all

SELECT t4.CATEGORYNAME AS "Categoryname", t1.PRODUCTNAME AS "Productname", t3.CO
MPANYNAME AS "Companyname", t2.CONTACTNAME AS "Contactname"
FROM TEST.PRODUCTS t1
INNER JOIN TEST.SUPPLIERS t2 ON t1.SUPPLIERID = t2.SUPPLIERID
INNER JOIN TEST.SUPPLIERS t3 ON t1.SUPPLIERID = t3.SUPPLIERID
INNER JOIN TEST.CATEGORIES t4 ON t1.CATEGORYID = t4.CATEGORYID

As you can see there are two major problems:
- first, left outer join vs inner join. As the two columns(supplierid and categoryid) are nullable, I was expecting left join, like microsoft linq does.
- second, there are as many inner join as referenced columns in the query: I take two fields from the supplier table and it makes two join on the supplier table, which is wrong.

Maybe I missed something, but could you have a look at this case?

best regards,

laurent

[lnu]

Did I miss something or is it a bug?

[Shalex]

The IsForeignKey flag of the Association attribute in the object property of DataContext determines what to use in runtime: whether LEFT OUTER JOIN or INNER JOIN. Microsoft's model should work in the same way if settings are identical. Please send us (support*devart*com) your attribute entries. We will work on the issue when JOIN is duplicated.

[lnu]

Ok

so for the left join part here are my configurations:

SQL server/Microsoft Linq/Vs designer:

Code: Select all

[Association(Name="Category_Product", Storage="_Category", ThisKey="CategoryID", OtherKey="CategoryID", IsForeignKey=true, DeleteRule="CASCADE")]
[Association(Name="Supplier_Product", Storage="_Supplier", ThisKey="SupplierID", OtherKey="SupplierID", IsForeignKey=true, DeleteRule="CASCADE")]

Oracle/Devart Linq/Entity Developer:

Code: Select all

[Association(Name="Category_Product", Storage="_Category", ThisKey="Categoryid",OtherKey="Categoryid", IsForeignKey=true,DeleteRule="CASCADE")]
[Association(Name="Supplier_Product", Storage="_Supplier", ThisKey="Supplierid",OtherKey="Supplierid", IsForeignKey=true,DeleteRule="CASCADE")]

It looks pretty much the same.

If I change the IsForeignKey property to false, the sql generated containes LEFT JOIN, as wanted. But, what drives the Left or inner join decision should be that the ThisKey property of the class is nullable or not, as in the microsoft implementation?

Regarding the multiple join problem, with the property modified I get multiple left join instead of multiple inner join.

best regards,

laurent

[Shalex]

Usage of INNER or LEFT OUTER depends on the IsForeingKey parameter of Association and the CanBeNull parameter of the ThisKey column in Microsoft's implementation (INNER is used if IsForeignKey ==true && CanBeNull ==false). We will implement the corresponding behaviour of our provider in the next build.

The duplicated JOINs issue will be fixed too. But no timeframe can be provided in this case.

[PangoChris]

I just wanted to confirm for anyone else reading this that upgrading from dotConnect for Oracle v 5.25.42 to 5.25.44. fixes the problem of inner joins being generated instead of left outer joins. It would be nice if the dotConnect revision histories listed more bugfixes, as I (and others, I suspect) only want to go through the trouble of upgrading if there's a relevant fix. In this case, the only reason I upgraded to the new version was to see if join generation was still broken before posting a bug report on the off chance that it didn't make its way into the public revision history. I shouldn't have to guess at revisions like that.

Anyway, left outer join is generated if the foreign key is nullable and inner join is generated otherwise. Good job.


Chris