Joining tables + where not working

[kardosbalint]

Hi,

I've compiled revision #1272, and would like to use the following:

var items = from incomes in db.Incomes
join items in db.Items on incomes.ItemID equals incomes.ID
join prices in db.Prices on item.ID equals prices.ItemID
where prices.Price > 1000
orderby items.ModDate descending
select new { incomes, items, prices };


and it throws an ArgumentException on "New" in SqlProvider.cs @ line
314.

How can I make it work?

Thanks,

b.

[Jonathan Pryor]

Two questions...

1. What database is this with?

On Sat, 2009-11-14 at 11:46 -0800, kardosbalint wrote:
> I've compiled revision #1272, and would like to use the following:
>
> var items = from incomes in db.Incomes
> join items in db.Items on incomes.ItemID equals incomes.ID
> join prices in db.Prices on item.ID equals prices.ItemID
> where prices.Price > 1000
> orderby items.ModDate descending
> select new { incomes, items, prices };

2. This query doesn't make sense to me; is it correct?

Specifically, this:

join items in db.Items on incomes.ItemID equals incomes.ID

It appears to want a join for where incomes.ItemID == incomes.ID (notice
that 'incomes' is used in both incomes.ItemID and incomes.ID), and it
seems rather odd that you'd have a "self-referential" DB schema (where a
column value on a row references the primary key of the same row).

This seems...odd.

Finally, there are several known issues with Joins in DbLinq; see:

http://dblinq2007.googlecode.com/svn/trunk/src/DbLinq/Test/Providers/Linq_101_Samples/Join.cs

Specifically, any test with '#if !DEBUG && ...' is failing for at least
one DB provider (e.g. LinqToSqlJoin05() fails for Postgre). It's
possible that your case is similar to one of those, but I wasn't able to
get my mind wrapped around your test to see which of those existing
tests, if any, was a close match to your query.

The closest query I could write to yours, while using Northwind types,
is this:

var q = from e in db.Employees
join o in db.Orders on e.Region equals o.ShipRegion
join c in db.Customers on o.CustomerID equals c.CustomerID
where e.BirthDate.HasValue
orderby e.BirthDate descending
select new {e, o, c};

Unfortunately, that doesn't have any runtime failures in SQL Server.

Thanks,
- Jon