ansaurus

Question

LINQ to SQL: inner join with manual association on SQL 2000

Answer 1

A:

hey, not sure i completely understood your question, but here's some info,

By default LINQ to SQL adopts lazy binding, meaning it won't query the database until it needs to. That's why you're getting multiple queries.

There are few things you can do to avoid multiple hits on the DB:

You can turn off lazy binding globally in the LINQ to SQL designer. But then you'll always perform JOINS if your tables have any relationships.
You do the JOIN manually in the LINQ query. If you do this, you'll have to specify which "fields" you want to return in your LINQ query to get back the JOINED data. And if you do this, then you'll be returning an Anonymous type

NOTE: LINQ to SQL doesn't support LEFT JOINS. If you google LINQ to SQL Left Joins you'll see plenty of info on this particular subject

cheers, and good luck!

andy 2009-02-04 23:13:42

Lazy loading doesn't apply to his situation, becaue he is using the DataLoadOptions

RobS 2009-02-04 23:22:40

do my recommendations still apply, or is there something else going on?

andy 2009-02-04 23:32:26

It's hard to say without seeing the DBML/Entity definitions - it's unclear how he manually created the association. I'd agree with #2 if he is trying to return a subset - depends how he has modelled it

RobS 2009-02-05 04:10:58

hmm...LINQ To SQL, you gotta love it

andy 2009-02-05 07:02:40

Rob, that is exactly why i'm stumped.

Paul Lemke 2009-02-05 16:56:44

lemkepf,could you not apply #2?

andy 2009-02-05 22:12:26

I'm going to try #2 today sometime.

Paul Lemke 2009-02-06 13:42:08

I'm doing something wrong... I execute this with the load options: Dim dbproducst = (From p In db.commerce_products Select New With {.product = p, .qtybreak = p.commerce_qty_breaks}).ToListIt executes 1 select for commerce_product, then 10000+ to get the commerce_qty_breaks.

Paul Lemke 2009-02-06 16:46:02

Answer 2

+1 A:

Hi There. I created a VB console app and created the schema as you have it here.

Also - the relationship is PK -> PK so does this mean it's supposed to be a one-to-one relationship?

I populated the tables with a row each (see below) and ran the code you've listed above. I ran SQL Profiler and it only queried once:

SELECT [t0].[pf_id], [t0].[name], [t0].[description], [t0].[restricted], [t1].[pf_id] AS [pf_id2], [t1].[sku], [t1].[qty], [t1].[list_price], [t1].[break_id], ( SELECT COUNT(*) FROM [dbo].[commerce_qty_break] AS [t2] WHERE [t2].[pf_id] = [t0].[pf_id] ) AS [value] FROM [dbo].[commerce_product] AS [t0] LEFT OUTER JOIN [dbo].[commerce_qty_break] AS [t1] ON [t1].[pf_id] = [t0].[pf_id] ORDER BY [t0].[pf_id], [t1].[qty], [t1].[break_id]

I wanted to make sure that the Data Options was forcing a deep load, so I added some extra code - here's the full code I used (and only the single query as above was traced):

Dim options As New DataLoadOptions()

options.LoadWith(Function(c As commerce_product) c.commerce_qty_breaks)
db.LoadOptions = options

Dim dbProducts = (From prods In db.commerce_products).ToList

Dim dbProduct = dbProducts.First().commerce_qty_breaks
Dim x = dbProduct.First().list_price

Here's the test data:

INSERT INTO [Test].[dbo].[commerce_product] ([pf_id],[name],[description],[restricted]) VALUES (1,'Test','Test','Test')
GO
INSERT INTO [Test].[dbo].[commerce_qty_break] ([pf_id],[sku],[qty],[list_price],[break_id]) VALUES (1,'22',1,1,1)
GO

RobS 2009-02-07 07:22:27

This was run against SQL Server 2008 from Visual Studio 2008 SP 1 (and running on .Net Framework 3.5)

RobS 2009-02-07 09:09:38

Interesting... I copied the database to a SQL 2008 server and it worked just fine! The database currently lives on SQL 2000. Why would this matter? I thought the only diff for linq to sql was the "take" method?

Paul Lemke 2009-02-09 15:53:57

Yeah same as my understanding also. I'll point it at a SQL 2000 db and see if I get the same result.

RobS 2009-02-10 22:59:21

Answer 3

A:

Go grab LINQPad. It's great for playing with stuff like this.

Did you try something like this?

Dim dbproducts = From prods In db.commerce_products _
    Join qtys In db.commerce_qty_breaks On prods.pf_id Equals qtys.pf_id _
    Select new {prods, qtys}

D. Lambert 2009-02-11 16:58:20

Answer 4

A:

I ended up using the entity framework and all is well.

Paul Lemke 2009-08-25 19:00:43

ansaurus

tags:

views:

answers:

LINQ to SQL: inner join with manual association on SQL 2000

related questions