How can I use association in linq2db

Question

I try to use linq2db with firebird sql server. I have two tables, which have releation.

    [Table("REQUESTS")]
    public partial class Request
    {
        [Column("ID")]
        [PrimaryKey]
        public int Id { get; set; }

        [Column("LATEST_REQUEST_DATA_ID")]
        public int? LatestRequestDataId { get; set; }

        [Association(ThisKey="LATEST_REQUEST_DATA_ID", OtherKey="ID")]
        public virtual RequestData LatestData { get; set; }
    }

    [Table("REQUEST_DATA")]
    public class RequestData
    {

        [Column("ID")]
        [PrimaryKey]
        public int Id { get; set; }

        [Column("REQUEST_ID")]
        public int RequestId { get; set; }
    }

    public class RequestDb : DataConnection
    {
        public ITable<Request> Requests { get { return GetTable<Request>(); } }
        public ITable<RequestData> Data { get { return GetTable<RequestData>(); } }
    }

    ...
        using (var context = new RequestDb())
        {

            var r = context.Requests.FirstOrDefault();
            var d = context.Data.FirstOrDefault(dd => dd.Id == r.LatestRequestDataId);
            Console.WriteLine(r);
        }

And I get d, which have the result and after I try to get result from r.LatestData but I get r.LatestData is null Why did I get r.LatestData = null?

Answer 1

Your code returns null because Linq2Db doesn't take association in account during materialization (only if you pointed out manually). This is done to: 1) avoid cycling (as you remember there is no heavy DataContext-so it creates each time new class) 2) to optimize queries (not to make unnecessary joins). So in your sample you have to write:

r=context.Requests.LoadWith(request=>
request.LatestData).FirstOrDefault();