How to add a relationship between tables in LINQ to DB model class

Question

I'm using LINQ to DB (linq2db) and I have a class, Activity.cs which has a Customer property, like this:

public Customer Customer { get; set; }

The Customer class:

    public class Customer
{
    [PrimaryKey, Identity]
    [Column(Name = "CustomerId"), NotNull]
    public string Id { get; set; }

    [Column(Name = "Name")]
    public string Name { get; set; }
}

Now, I want to be able to do something like this:

db.Activities.First().Customer.Name  //Returns the customer name of an activity

How can I set the relationship between the entities, such that I can do as explained above?

(Yes, I know it doesn't make sense to have the Id field as a string, I have to work against a messy legacy Access database)

Answer 1

If I good understand, one Activity has one Customer. If so, you should add to your Activity class a relation:

[Table( Name = "Customers" )]
public class Customer
{
    [PrimaryKey, Identity]
    [Column(Name = "CustomerId"), NotNull]
    public string Id { get; set; }

    [Column(Name = "Name")]
    public string Name { get; set; }
}

[Table( Name = "Activities" )]
public class Activity
{
    [PrimaryKey, Identity]
    [Column(Name = "ActivityId"), NotNull]
    public string Id { get; set; }

    [Column( Name = "Customer" )] 
    private int? customerId; 

    private EntityRef<Customer> _customer = new EntityRef<Customer>( );

    [Association(IsForeignKey = true, Storage = "_customer", ThisKey = "customerId" )]
    public Customer Customer{
        get { return _customer.Entity; }
        set { _customer.Entity = value; }
    }
}

A good article about this subject

EDIT:

A walk-around when association does not work:

[Table( Name = "Activities" )]
public class Activity
{
    [PrimaryKey, Identity]
    [Column(Name = "ActivityId"), NotNull]
    public string Id { get; set; }

    [Column( Name = "CustomerId" )] 
    public int? CustomerId; 

}

You can retrieve a customer from activity like this:

var activity = db.Activities.FirstOrDefault()
var customer = db.Customers.FirstOrDefault(c => c.Id = activity.CustomerId);

Answer 2

This question is about the use of Linq2db, not of LinqToSQL. Linq2db is an alternative ORM than supports others database types like Postgresql or SQLite. Linq2db is optimized for not consume many resources, therefore to attempt to directly access the db.Activities.First().Customer.Name, always will throw an exception because it will not load the value of Costomer.

For example, if we use the following code to query the Customers of the Northwind database,

using (var db = new NorthwindDB())
        {
            var q = from c in db.Customers
                    select c;
        }

It would generate a SQL query like the following:

{-- NorthwindDB SqlServer.2008
SELECT
    [t1].[CustomerID],
    [t1].[CompanyName],
    [t1].[ContactName],
    [t1].[ContactTitle],
    [t1].[Address],
    [t1].[City],
    [t1].[Region],
    [t1].[PostalCode],
    [t1].[Country],
    [t1].[Phone],
    [t1].[Fax]
FROM
    [dbo].[Customers] [t1]
}

Although the Customer entity has a one-to-many relationship with the Order entity they are not going to load these values. For the case that we want to get their Customer Orders, I recommend using an unknown type (or simple class) as the following query.

using (var db = new NorthwindDB())
        {
            var q = from c in db.Customers
                    select new
                    {
                        c.CompanyName,
                        EmployeeName = c.Orders.First().Employee.FirstName
                    };
        }

It would generate a SQL query like the following:

{-- NorthwindDB SqlServer.2008
SELECT
    [t4].[CompanyName],
    [t3].[FirstName] as [FirstName1],
    [t3].[c1] as [c11]
FROM
    [dbo].[Customers] [t4]
        OUTER APPLY (
            SELECT TOP (1)
                [t1].[FirstName],
                1 as [c1]
            FROM
                [dbo].[Orders] [t2]
                    LEFT JOIN [dbo].[Employees] [t1] ON [t2].[EmployeeID] = [t1].[EmployeeID]
            WHERE
                [t4].[CustomerID] = [t2].[CustomerID]
        ) [t3]
}