How do you define a type in a Linq 2 SQL mapping?

Question

I'm trying to do my linq 2 sql objects manually, so I have the following code:

var mapping = XmlMappingSource.FromXml(xml);

using (DataContext ctx = new DataContext(conn_string, mapping))
{
    list = ctx.GetTable<Achievement>().ToList();
}

and the XML looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<Database Name="FatFights" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007"&gt;
  <Table Name="dbo.Achievements">
    <Type Name="FatFights.Business.Objects.Achievement">
      <Column Name="Id" Member="Id" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" />
      <Column Name="UserId" Member="UserId" />
      <Column Name="Achieved" Member="Achieved" />
      <Column Name="AchievementId" Member="AchievementTypeId" />
      <Association Name="AchievementType_Achievement" Member="AchievementTypeId" ThisKey="Id" OtherKey="Id" IsForeignKey="true" />
    </Type>
  </Table>
</Database>

this returns the following error:

System.InvalidOperationException: Could not find key member 'Id' of key 'Id' on type 'Int32'. The key may be wrong or the field or property on 'Int32' has changed names..

So I need to figure out how to tell Linq 2 SQL that Id is a GUID not an Int32... So I generated some Linq2SQL XML to see how they do it and they pass Type, but Type isn't a valid attribute according to the XSD, so it fails.

Here is the SQL table:

CREATE TABLE Achievements
(
    Id              UNIQUEIDENTIFIER    NOT NULL    CONSTRAINT RG_Achievements ROWGUIDCOL
                                                    CONSTRAINT DF_Achievements_Id DEFAULT (NEWID()),
    UserId          UNIQUEIDENTIFIER    NOT NULL,
    AchievementId   INTEGER             NOT NULL,
    Achieved        DATETIME            NOT NULL,

    CONSTRAINT FK_Achievements_Users
        FOREIGN KEY (UserId)
        REFERENCES aspnet_Users (UserId),

    CONSTRAINT FK_Achievements_AcheivementTypes
        FOREIGN KEY (AchievementId)
        REFERENCES AchievementTypes (Id),

    CONSTRAINT PK_Achievements
        PRIMARY KEY (Id),

    CONSTRAINT UQ_Achievements_1
        UNIQUE (UserId, AchievementId)
)

and the business object:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace FatFights.Business.Objects
{
    public class Achievement
    {
     public Guid Id { get; set; }
     public Guid UserId { get; set; }
     public int AchievementTypeId { get; set; }
     public DateTime Achieved { get; set; }
    }
}

Answer 1

I suspect the issue is here:

Member="AchievementTypeId"

For an association, you should be linking a typed member - for example you might have a property called "AchievementType" (of type AchievementType), and have Member="AchievementType".

For example, in Northwind, linking Customer and Order shows (for Order):

<Association Name="Customer_Order" Member="Customer"
  ThisKey="CustomerID" OtherKey="CustomerID"
  Type="Customer" IsForeignKey="true" />

The SqlMetal generated code then has some overly complex code linking the CustomerID and Customer properties.