Setting SQL Relationships For LINQ Queries

Question

I have two tables that I'm trying to create a relationship between so I can write nice LINQ queries that don't require join.

Widgets
WidgetId
WidgetDescription

Orders
OrderId
WidgetId
OrderDate

What I want to be able to do is create a LINQ query that does something similar to:

var result = from x in db.Widgets
Where x.Orders.OrderDate == "5/11/2010"
select x;

I can't seem to get intellitext to pick up the other database despite creating a relationship in SQL server. Are there any additional steps I need to take to make this work?

Answer 1

I don't think it could be done without either adding OrderDate to Widgets in your database, or running a join query before querying it.

I would do something like this.

var firstQuery = (for w in Widgets
                  join o in Orders
                  on w.WidgetId equals o.WidgetID
                  select new {Widgets = w, OrderDate = o.OrderDate}).Distinct();

var secondQuery = for record in firstQuery
                  where record.OrderDate == "5/11/2010"
                  select record.Widgets;

This way you get an OrderDate for each Widget record.

Answer 2

How about something like:

var result = from widgetId in 
                (from order in orderContext.Orders
                    where order.OrderDate == new DateTime(2010, 5, 11)
                    select order.WidgetId)
            from widget in widgetContext.Widgets
            where widget.WidgetId = widgetId
            select widget;