Invalid query generation: missing JOIN

[GoogleCodeExporter]

1.
Create two table. Items and Users.
The table Items has a foreign-key to User (not nullable)
Example: Item.UserId that maps to User.Id
The association is one-to-many. DataType: Int32

2.
Create the relative DBML file with the association:
Table Items
      <Association Name="User_Item" Member="Owner" ThisKey="UserId"
OtherKey="Id" Type="User" IsForeignKey="true" />
Table User
      <Association Name="User_Item" Member="Items" ThisKey="Id"
OtherKey="UserId" Type="Item" />

and generate the source code file.

3. Execute a simple query like this:
            var x = from i in db.Items
                    select i.Owner;

The generated query is:
SELECT `firstname`, `hashed_password`, `id`, `lastname`, `login`, `mail`
FROM `db`.`users`
WHERE (`id` = i$.`user_id`)

As you can see the JOIN is missing. So i get an exception:
"Unknown column 'i$.user_id' in 'where clause'"

If then i force the JOIN with the following query:
        var data = from i in db.Items
                         from u in db.Users
                         where i.UserID == u.ID
                         select u;

I get the following exception:
Argument type 'DbLinq.Data.Linq.Table`1[<Namespace>.User]' does not match
the corresponding member type '<Namespace>.User'

I'm using DbLinq-0.20.1. with MySQL Provider.

Original issue reported on code.google.com by Bon...@gmail.com on 5 May 2010 at 10:41

[GoogleCodeExporter]

I think i have the same issue but with SQLite,

Tables : 
LineNodes: has Id, LineId, and NodeId, foreign keyed to Line and Node tables

Execute the following query:
var lineQuery = from lineNode in db.LineNodes
         select lineNode.Line;

Generates:

SELECT "Id", "Name", "OneWay", "TypeId"
FROM "main"."Line"
WHERE ("Id" = lineNode$."LineId")

which throws an exception (it's not joining on the LineNode table)

Original comment by ofer.ach...@gmail.com on 12 Jun 2010 at 6:04

[GoogleCodeExporter]

The following patch appears to fix the issue:

Index: 
src/DbLinq/Data/Linq/Sugar/Implementation/ExpressionDispatcher.Analyzer.cs
===================================================================
--- 
src/DbLinq/Data/Linq/Sugar/Implementation/ExpressionDispatcher.Analyzer.cs  (revi
sion 1410)
+++ 
src/DbLinq/Data/Linq/Sugar/Implementation/ExpressionDispatcher.Analyzer.cs  (work
ing copy)
@@ -668,11 +668,11 @@
                     string.Format("Explicit construction of entity type '{0}' in query is not allowed.",
                         ex.Type.FullName));
             TableExpression tableExpression = parameters[0] as TableExpression;
-            if (tableExpression != null && 
builderContext.CurrentSelect.Tables.Count == 0)
-                RegisterTable(tableExpression, builderContext);
+           if (tableExpression != null && (builderContext.CurrentSelect.Tables.Count 
== 0 || tableExpression.JoinType == TableJoinType.Inner))
+                   RegisterTable(tableExpression, builderContext);
             return ex;
         }
-
+       
         /// <summary>
         /// Entry point for a Where()
         /// static Where(this Expression table, λ(table))

Original comment by ofer.ach...@gmail.com on 4 Jul 2010 at 5:08