title: LINQ to SQL: inner join with manual association on SQL 2000
body: <p>I have two tables in a one to many relationship. (products and qty break pricing). At the database level I cannot create a relationship between the two tables. I brought those two tables into LINQ and created the association manually. </p>
  
  <p>I need to do a big LINQ query and have the tables be joined. My problem is it's not using a join to get the data. LINQ is using 1 select on the main table, then 1 select for each row in that main table. </p>
  
  <pre><code>Dim db As New LSSStyleDataContext(connString)
  
  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
  </code></pre>
  
  <p>Any thoughts on why this might be? Thanks!
  Paul </p>
  
  <p>EDIT: here are the two tables:</p>
  
  <pre><code>CREATE TABLE [dbo].[commerce_product](
      [pf_id] [int] NOT NULL,
      [name] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS         
      [description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [restricted] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT [PK_commerce_product_1] PRIMARY KEY NONCLUSTERED 
   (
      [pf_id] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  </code></pre>
  
  <p>And the other table: </p>
  
  <pre><code>CREATE TABLE [dbo].[commerce_qty_break](
      [pf_id] [int] NOT NULL,
      [sku] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [qty] [int] NOT NULL,
      [list_price] [int] NOT NULL,
      [break_id] [int] NOT NULL,
      CONSTRAINT [PK_commerce_qty_break] PRIMARY KEY CLUSTERED 
      (
      [pf_id] ASC,
      [qty] ASC,
      [break_id] ASC
      ) ON [PRIMARY]
      ) ON [PRIMARY]
  </code></pre>
  
  <p>The DBML is straight forward with only the two tables. I created an association between the two tables, "commerce_product" being the parent and "commerce_qty_break" being the child joined by "PF_ID". </p>
  
  <p>I can write something like this:</p>
  
  <pre><code>Dim dbproducts = From prods In db.commerce_products _
      Join qtys In db.commerce_qty_breaks On prods.pf_id Equals qtys.pf_id _
      Select prods
  </code></pre>
  
  <p>And i see that it joins on the table in the query, but as soon as i try and spin through the "qty_breaks" it starts executing selects to get that info. </p>
  
  <p>I'm totally stumped. </p>
  
  <p>Edit 2: Here is the DBML: </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;Database Name="LSScommerceDB_DevB" Class="LSSStyleDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007"&gt;
    &lt;Connection Mode="AppSettings" ConnectionString="***" SettingsObjectName="HSLPriceUpdate.My.MySettings" SettingsPropertyName="LSScommerceDB_DevBConnectionString" Provider="System.Data.SqlClient" /&gt;
    &lt;Table Name="dbo.commerce_product" Member="commerce_products"&gt;
      &lt;Type Name="commerce_product"&gt;
        &lt;Column Name="pf_id" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" /&gt;
        &lt;Column Name="name" Type="System.String" DbType="VarChar(500)" CanBeNull="true" /&gt;
        &lt;Column Name="description" Type="System.String" DbType="Text" CanBeNull="true" UpdateCheck="Never" /&gt;
        &lt;Column Name="list_price" Type="System.Int32" DbType="Int" CanBeNull="true" /&gt;
        &lt;Column Name="image_file" Type="System.String" DbType="VarChar(255)" CanBeNull="true" /&gt;
        &lt;Column Name="image_width" Type="System.Int32" DbType="Int" CanBeNull="true" /&gt;
        &lt;Column Name="image_height" Type="System.Int32" DbType="Int" CanBeNull="true" /&gt;
        &lt;Column Name="sale_price" Type="System.Int32" DbType="Int" CanBeNull="true" /&gt;
        &lt;Column Name="sale_start" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /&gt;
        &lt;Column Name="sale_end" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /&gt;
        &lt;Column Name="attr_label1" Type="System.String" DbType="VarChar(100)" CanBeNull="true" /&gt;
        &lt;Column Name="attr_label2" Type="System.String" DbType="VarChar(100)" CanBeNull="true" /&gt;
        &lt;Column Name="attr_label3" Type="System.String" DbType="VarChar(100)" CanBeNull="true" /&gt;
        &lt;Column Name="attr_label4" Type="System.String" DbType="VarChar(100)" CanBeNull="true" /&gt;
        &lt;Column Name="attr_label5" Type="System.String" DbType="VarChar(100)" CanBeNull="true" /&gt;
        &lt;Column Name="sku" Type="System.String" DbType="VarChar(100)" CanBeNull="true" /&gt;
        &lt;Column Name="UOM" Type="System.String" DbType="VarChar(50)" CanBeNull="true" /&gt;
        &lt;Column Name="Sell_Pack" Type="System.String" DbType="VarChar(50)" CanBeNull="true" /&gt;
        &lt;Column Name="mfg_model_number" Type="System.String" DbType="VarChar(50)" CanBeNull="true" /&gt;
        &lt;Column Name="mfg_id" Type="System.Int32" DbType="Int" CanBeNull="true" /&gt;
        &lt;Column Name="logo_file" Type="System.String" DbType="VarChar(255)" CanBeNull="true" /&gt;
        &lt;Column Name="drop_ship" Type="System.String" DbType="VarChar(50)" CanBeNull="true" /&gt;
        &lt;Column Name="lead_time" Type="System.Int32" DbType="Int" CanBeNull="true" /&gt;
        &lt;Column Name="hazard_flag" Type="System.String" DbType="VarChar(50)" CanBeNull="true" /&gt;
        &lt;Column Name="publish_date" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /&gt;
        &lt;Column Name="restricted" Type="System.String" DbType="VarChar(5)" CanBeNull="true" /&gt;
        &lt;Association Name="commerce_product_commerce_qty_break" Member="commerce_qty_breaks" ThisKey="pf_id" OtherKey="pf_id" Type="commerce_qty_break" /&gt;
      &lt;/Type&gt;
    &lt;/Table&gt;
    &lt;Table Name="dbo.commerce_qty_break" Member="commerce_qty_breaks"&gt;
      &lt;Type Name="commerce_qty_break"&gt;
        &lt;Column Name="pf_id" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" /&gt;
        &lt;Column Name="sku" Type="System.String" DbType="VarChar(100) NOT NULL" CanBeNull="false" /&gt;
        &lt;Column Name="qty" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" /&gt;
        &lt;Column Name="list_price" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" /&gt;
        &lt;Column Name="sale_price" Type="System.Int32" DbType="Int" CanBeNull="true" /&gt;
        &lt;Column Name="sale_start" Type="System.DateTime" DbType="DateTime NOT NULL" CanBeNull="false" /&gt;
        &lt;Column Name="sale_end" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /&gt;
        &lt;Column Name="break_id" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" /&gt;
        &lt;Association Name="commerce_product_commerce_qty_break" Member="commerce_product" ThisKey="pf_id" OtherKey="pf_id" Type="commerce_product" IsForeignKey="true" /&gt;
      &lt;/Type&gt;
    &lt;/Table&gt;
  &lt;/Database&gt;
  </code></pre>
  
  <p>EDIT 3: Apparently this is only an issue in SQL 2000. SQL 2008 works fine. I have other tables that do eager loading in SQL 2000 and i can't figure out what the difference is between these two tables. </p>