I have an existing working codebase that I'm converting from BLToolkit to Linq2Db. In the existing codebase/database there is a table using single-table inheritance. This is the part of the code that I'm having difficulty converting to Linq2Db.
The issue I believe stems from a 3-deep class hierarchy rather than the simplest 2-deep class hierarchy. There is a base abstract class and a single intermediate abstract class, from which concrete classes inherit one or the other.
I'm not sure if I'm missing something subtle to give Linq2Db the info it needs to understand the data model, or if I've run into a Linq2Db bug for an edge case that doesn't get used often. I'm also making an assumption that Linq2Db should be able to support a 3-deep class hierarchy since its' predecessor BLToolkit did and I haven't seen anything so far that says Linq2Db doesn't.
To summarize the question, in a Linq2Db database context, how can I add a working reference to the collection of objects that inherit from an intermediate abstract class similar to how you can with the base abstract class when using single table inheritance?
Current Code
The real code has roughly a dozen concrete classes in the class hierarchy for the single table inheritance, so I've created a simplified version for testing purposes.
[LinqToDB.Mapping.Table("Things", IsColumnAttributeRequired = false)]
[LinqToDB.Mapping.InheritanceMapping(Code = 1, Type = typeof(ConcreteBaseThingAlpha))]
[LinqToDB.Mapping.InheritanceMapping(Code = 2, Type = typeof(ConcreteBaseThingBeta))]
[LinqToDB.Mapping.InheritanceMapping(Code = 101, Type = typeof(ConcreteIntermediateThingOne))]
[LinqToDB.Mapping.InheritanceMapping(Code = 102, Type = typeof(ConcreteIntermediateThingTwo))]
public abstract class BaseThing
{
[LinqToDB.Mapping.PrimaryKey]
public long Id { get; set; }
[LinqToDB.Mapping.Column(IsDiscriminator = true)]
public int Type { get; set; }
public int BaseField { get; set; }
}
public class ConcreteBaseThingAlpha : BaseThing
{
}
public class ConcreteBaseThingBeta : BaseThing
{
public int ConcreteField { get; set; }
}
public abstract class IntermediateThing : BaseThing
{
public int IntermediateField { get; set; }
}
public class ConcreteIntermediateThingOne : IntermediateThing
{
public int ConcreteField { get; set; }
}
public class ConcreteIntermediateThingTwo : IntermediateThing
{
}
[LinqToDB.Mapping.Table("Persons", IsColumnAttributeRequired = false)]
public class Person
{
[LinqToDB.Mapping.PrimaryKey]
public long Id { get; set; }
[LinqToDB.Mapping.NotNull]
public string FullName { get; set; }
}
[LinqToDB.Mapping.Table("Interactions", IsColumnAttributeRequired = false)]
public class Interaction
{
[LinqToDB.Mapping.PrimaryKey, LinqToDB.Mapping.Identity]
public long Id { get; set; }
public DateTimeOffset Timestamp { get; set; }
public long PersonId { get; set; }
public long ThingId { get; set; }
}
Ideally, the database context would be:
public partial class ThingsContext : LinqToDB.Data.DataConnection
{
public ThingsContext()
: base(new DataOptions()
.UseSqlServer(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\temp\ThingsDb.mdf;Integrated Security=True"))
{
}
public virtual ITable<BaseThing> BaseThings
{
get
{
return this.GetTable<BaseThing>();
}
}
public virtual ITable<IntermediateThing> IntermediateThings
{
get
{
return this.GetTable<IntermediateThing>();
}
}
public virtual ITable<Person> Persons
{
get
{
return this.GetTable<Person>();
}
}
public virtual ITable<Interaction> Interactions
{
get
{
return this.GetTable<Interaction>();
}
}
}
And then to get a list of all things that are of type IntermediateThing but not of types ConcreteBaseThingAlpha or ConcreteBaseThingBeta would be:
using var db = new ThingsContext();
var things = db.IntermediateThings.ToList();
However, this throws an exception: System.InvalidOperationException : Can't compile a NewExpression with a constructor declared on an abstract class
Alternatives Attempted
I've created an xUnit test project with integration tests on various alternate ways to obtain the same set of data. However, each attempt has only been partially successful.
using LinqToDB;
namespace LinqToDb.Tests
{
public partial class ThingsContext : LinqToDB.Data.DataConnection
{
public ThingsContext()
: base(new DataOptions()
.UseSqlServer(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\temp\ThingsDb.mdf;Integrated Security=True"))
{
}
// No issue with base type
public virtual ITable<BaseThing> BaseThings
{
get
{
return this.GetTable<BaseThing>();
}
}
// 2/4 tests passing
public virtual ITable<IntermediateThing> IntermediateThings
{
get
{
return this.GetTable<IntermediateThing>();
}
}
// 2/4 tests passing
public virtual IQueryable<IntermediateThing> OfTypeIntermediateThings
{
get
{
return this.GetTable<BaseThing>()
.OfType<IntermediateThing>();
}
}
// 3/4 tests passing
public virtual IQueryable<IntermediateThing> WhereCastIntermediateThings
{
get
{
return this.GetTable<BaseThing>()
.Where(x => x.Type == 101 || x.Type == 102)
.Cast<IntermediateThing>();
}
}
// 1/4 tests passing
public virtual IQueryable<IntermediateThing> WhereSelectIntermediateThings
{
get
{
return this.GetTable<BaseThing>()
.Where(x => x.Type == 101 || x.Type == 102)
.Select(x => (IntermediateThing)x);
}
}
// 4/4 tests passing; however, it is not the correct return type.
public virtual IQueryable<BaseThing> WhereIntermediateThings
{
get
{
return this.GetTable<BaseThing>()
.Where(x => x.Type == 101 || x.Type == 102);
}
}
// Included only for join tests
public virtual ITable<Person> Persons
{
get
{
return this.GetTable<Person>();
}
}
// Included only for join tests
public virtual ITable<Interaction> Interactions
{
get
{
return this.GetTable<Interaction>();
}
}
}
[LinqToDB.Mapping.Table("Things", IsColumnAttributeRequired = false)]
[LinqToDB.Mapping.InheritanceMapping(Code = 1, Type = typeof(ConcreteBaseThingAlpha))]
[LinqToDB.Mapping.InheritanceMapping(Code = 2, Type = typeof(ConcreteBaseThingBeta))]
[LinqToDB.Mapping.InheritanceMapping(Code = 101, Type = typeof(ConcreteIntermediateThingOne))]
[LinqToDB.Mapping.InheritanceMapping(Code = 102, Type = typeof(ConcreteIntermediateThingTwo))]
public abstract class BaseThing
{
[LinqToDB.Mapping.PrimaryKey]
public long Id { get; set; }
[LinqToDB.Mapping.Column(IsDiscriminator = true)]
public int Type { get; set; }
public int BaseField { get; set; }
}
public class ConcreteBaseThingAlpha : BaseThing
{
}
public class ConcreteBaseThingBeta : BaseThing
{
public int ConcreteField { get; set; }
}
public abstract class IntermediateThing : BaseThing
{
public int IntermediateField { get; set; }
}
public class ConcreteIntermediateThingOne : IntermediateThing
{
public int ConcreteField { get; set; }
}
public class ConcreteIntermediateThingTwo : IntermediateThing
{
}
[LinqToDB.Mapping.Table("Persons", IsColumnAttributeRequired = false)]
public class Person
{
[LinqToDB.Mapping.PrimaryKey]
public long Id { get; set; }
[LinqToDB.Mapping.NotNull]
public string FullName { get; set; }
}
[LinqToDB.Mapping.Table("Interactions", IsColumnAttributeRequired = false)]
public class Interaction
{
[LinqToDB.Mapping.PrimaryKey, LinqToDB.Mapping.Identity]
public long Id { get; set; }
public DateTimeOffset Timestamp { get; set; }
public long PersonId { get; set; }
public long ThingId { get; set; }
}
public class InheritanceTests
{
/*
* Success
SELECT
[t1].[Id],
[t1].[Type],
[t1].[BaseField],
[t1].[ConcreteField],
[t1].[IntermediateField]
FROM
[Things] [t1]
*/
[Fact]
public void BaseThings_ToList()
{
using var db = new ThingsContext();
var things = db.BaseThings.ToList();
}
/*
* Success
SELECT
[b].[Type],
[p].[FullName]
FROM
[Things] [b]
INNER JOIN [Interactions] [i] ON [b].[Id] = [i].[ThingId]
INNER JOIN [Persons] [p] ON [i].[PersonId] = [p].[Id]
*/
[Fact]
public void BaseThings_Linq()
{
using var db = new ThingsContext();
var interactions =
(
from b in db.BaseThings
join i in db.Interactions on b.Id equals i.ThingId
join p in db.Persons on i.PersonId equals p.Id
select new
{
b.Type,
p.FullName
}
)
.ToList();
}
/*
* Success
exec sp_executesql N'UPDATE
[x]
SET
[x].[IntermediateField] = @IntermediateField
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102) AND
[x].[Id] = 3
',N'@IntermediateField int',@IntermediateField=333
*/
[Fact]
public void IntermediateThings_Update()
{
using var db = new ThingsContext();
db.IntermediateThings.Where(x => x.Id == 3).Set(y => y.IntermediateField, 333).Update();
}
/*
* Failure
* Missing ConcreteField in SELECT-clause and throws
* System.InvalidOperationException : Can't compile a NewExpression with a constructor declared on an abstract class.
exec sp_executesql N'SELECT TOP (@take)
[x].[Id],
[x].[Type],
[x].[BaseField],
[x].[IntermediateField]
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102) AND
[x].[Id] = 3
',N'@take int',@take=2
*/
[Fact]
public void IntermediateThings_Single()
{
using var db = new ThingsContext();
var thing = db.IntermediateThings.Single(x => x.Id == 3);
}
/*
* Failure
* Missing ConcreteField in SELECT-clause and throws
* System.InvalidOperationException : Can't compile a NewExpression with a constructor declared on an abstract class.
SELECT
[t1].[Id],
[t1].[Type],
[t1].[BaseField],
[t1].[IntermediateField]
FROM
[Things] [t1]
WHERE
([t1].[Type] = 101 OR [t1].[Type] = 102)
*/
[Fact]
public void IntermediateThings_ToList()
{
using var db = new ThingsContext();
var things = db.IntermediateThings.ToList();
}
/*
* Success
SELECT
[b].[Type],
[p].[FullName]
FROM
[Things] [b]
INNER JOIN [Interactions] [i] ON [b].[Id] = [i].[ThingId]
INNER JOIN [Persons] [p] ON [i].[PersonId] = [p].[Id]
WHERE
([b].[Type] = 101 OR [b].[Type] = 102)
*/
[Fact]
public void IntermediateThings_Linq()
{
using var db = new ThingsContext();
var interactions =
(
from b in db.IntermediateThings
join i in db.Interactions on b.Id equals i.ThingId
join p in db.Persons on i.PersonId equals p.Id
select new
{
b.Type,
p.FullName
}
)
.ToList();
}
/*
* Success
exec sp_executesql N'UPDATE
[x]
SET
[x].[IntermediateField] = @IntermediateField
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102) AND
[x].[Id] = 3
',N'@IntermediateField int',@IntermediateField=333
*/
[Fact]
public void OfTypeIntermediateThings_Update()
{
using var db = new ThingsContext();
db.OfTypeIntermediateThings.Where(x => x.Id == 3).Set(y => y.IntermediateField, 333).Update();
}
/*
* Failure
* Missing ConcreteField in SELECT-clause and throws
* System.InvalidOperationException : Can't compile a NewExpression with a constructor declared on an abstract class.
exec sp_executesql N'SELECT TOP (@take)
[x].[Id],
[x].[Type],
[x].[BaseField],
[x].[IntermediateField]
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102) AND
[x].[Id] = 3
',N'@take int',@take=2
*/
[Fact]
public void OfTypeIntermediateThings_Single()
{
using var db = new ThingsContext();
var thing = db.OfTypeIntermediateThings.Single(x => x.Id == 3);
}
/*
* Failure
* Missing ConcreteField in SELECT-clause and throws
* System.InvalidOperationException : Can't compile a NewExpression with a constructor declared on an abstract class.
SELECT
[t1].[Id],
[t1].[Type],
[t1].[BaseField],
[t1].[IntermediateField]
FROM
[Things] [t1]
WHERE
([t1].[Type] = 101 OR [t1].[Type] = 102)
*/
[Fact]
public void OfTypeIntermediateThings_ToList()
{
using var db = new ThingsContext();
var things = db.OfTypeIntermediateThings.ToList();
}
/*
* Success
SELECT
[b].[Type],
[p].[FullName]
FROM
[Things] [b]
INNER JOIN [Interactions] [i] ON [b].[Id] = [i].[ThingId]
INNER JOIN [Persons] [p] ON [i].[PersonId] = [p].[Id]
WHERE
([b].[Type] = 101 OR [b].[Type] = 102)
*/
[Fact]
public void OfTypeIntermediateThings_Linq()
{
using var db = new ThingsContext();
var interactions =
(
from b in db.OfTypeIntermediateThings
join i in db.Interactions on b.Id equals i.ThingId
join p in db.Persons on i.PersonId equals p.Id
select new
{
b.Type,
p.FullName
}
)
.ToList();
}
/*
* Success
exec sp_executesql N'UPDATE
[x]
SET
[x].[IntermediateField] = @IntermediateField
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102) AND
[x].[Id] = 3
',N'@IntermediateField int',@IntermediateField=333
*/
[Fact]
public void WhereCastIntermediateThings_Update()
{
using var db = new ThingsContext();
db.WhereCastIntermediateThings.Where(x => x.Id == 3).Set(y => y.IntermediateField, 333).Update();
}
/*
* Success
exec sp_executesql N'SELECT TOP (@take)
[x].[Id],
[x].[Type],
[x].[BaseField],
[x].[ConcreteField],
[x].[IntermediateField]
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102) AND
[x].[Id] = 3
',N'@take int',@take=2
*/
[Fact]
public void WhereCastIntermediateThings_Single()
{
using var db = new ThingsContext();
var thing = db.WhereCastIntermediateThings.Single(x => x.Id == 3);
}
/*
* Success
SELECT
[x].[Id],
[x].[Type],
[x].[BaseField],
[x].[ConcreteField],
[x].[IntermediateField]
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102)
*/
[Fact]
public void WhereCastIntermediateThings_ToList()
{
using var db = new ThingsContext();
var things = db.WhereCastIntermediateThings.ToList();
}
/*
* Failure
* Doesn't generate SQL and throws
* System.InvalidOperationException : No coercion operator is defined between types 'System.Int32' and 'Test.LinqToDb.Tests.IntermediateThing'.
*/
[Fact]
public void WhereCastIntermediateThings_Linq()
{
using var db = new ThingsContext();
var interactions =
(
from b in db.WhereCastIntermediateThings
join i in db.Interactions on b.Id equals i.ThingId
join p in db.Persons on i.PersonId equals p.Id
select new
{
b.Type,
p.FullName
}
)
.ToList();
}
/*
* Success
* Requires explicit casting for every reference.
exec sp_executesql N'UPDATE
[x]
SET
[x].[IntermediateField] = @IntermediateField
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102) AND
[x].[Id] = 3
',N'@IntermediateField int',@IntermediateField=333
*/
[Fact]
public void WhereIntermediateThings_Update()
{
using var db = new ThingsContext();
db.WhereIntermediateThings.Where(x => x.Id == 3).Set(y => ((IntermediateThing)y).IntermediateField, 333).Update();
}
/*
* Success
* Requires explicit casting for every reference.
exec sp_executesql N'SELECT TOP (@take)
[x].[Id],
[x].[Type],
[x].[BaseField],
[x].[ConcreteField],
[x].[IntermediateField]
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102) AND
[x].[Id] = 3
',N'@take int',@take=2
*/
[Fact]
public void WhereIntermediateThings_Single()
{
using var db = new ThingsContext();
var thing = (IntermediateThing)db.WhereIntermediateThings.Single(x => x.Id == 3);
}
/*
* Success
* Requires explicit casting for every reference.
SELECT
[x].[Id],
[x].[Type],
[x].[BaseField],
[x].[ConcreteField],
[x].[IntermediateField]
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102)
*/
[Fact]
public void WhereIntermediateThings_ToList()
{
using var db = new ThingsContext();
var things = db.WhereIntermediateThings.Select(x => (IntermediateThing)x).ToList();
}
/*
* Success
* Requires explicit casting for every reference.
SELECT
[x].[Type],
[x].[IntermediateField],
[p].[FullName]
FROM
[Things] [x]
INNER JOIN [Interactions] [i] ON [x].[Id] = [i].[ThingId]
INNER JOIN [Persons] [p] ON [i].[PersonId] = [p].[Id]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102)
*/
[Fact]
public void WhereIntermediateThings_Linq()
{
using var db = new ThingsContext();
var interactions =
(
from b in db.WhereIntermediateThings
join i in db.Interactions on b.Id equals i.ThingId
join p in db.Persons on i.PersonId equals p.Id
select new
{
b.Type,
((IntermediateThing)b).IntermediateField,
p.FullName
}
)
.ToList();
}
/*
* Failure
* Doesn't generate SQL and throws
* System.NotImplementedException : The method or operation is not implemented.
*/
[Fact]
public void WhereSelectIntermediateThings_Update()
{
using var db = new ThingsContext();
db.WhereSelectIntermediateThings.Where(x => x.Id == 3).Set(y => y.IntermediateField, 333).Update();
}
/*
* Failure
* Doesn't generate SQL and throws
* System.NotImplementedException : The method or operation is not implemented.
*/
[Fact]
public void WhereSelectIntermediateThings_Single()
{
using var db = new ThingsContext();
var thing = db.WhereSelectIntermediateThings.Single(x => x.Id == 3);
}
/*
* Success
SELECT
[x].[Id],
[x].[Type],
[x].[BaseField],
[x].[ConcreteField],
[x].[IntermediateField]
FROM
[Things] [x]
WHERE
([x].[Type] = 101 OR [x].[Type] = 102)
*/
[Fact]
public void WhereSelectIntermediateThings_ToList()
{
using var db = new ThingsContext();
var things = db.WhereSelectIntermediateThings.ToList();
}
/*
* Failure
* Doesn't generate SQL and throws
* System.NotImplementedException : The method or operation is not implemented.
*/
[Fact]
public void WhereSelectIntermediateThings_Linq()
{
using var db = new ThingsContext();
var interactions =
(
from b in db.WhereSelectIntermediateThings
join i in db.Interactions on b.Id equals i.ThingId
join p in db.Persons on i.PersonId equals p.Id
select new
{
b.Type,
b.IntermediateField,
p.FullName
}
)
.ToList();
}
}
}
The database I run this against has the following schema and data.
USE [master]
GO
CREATE DATABASE [ThingsDb]
GO
USE [ThingsDb]
GO
CREATE TABLE [dbo].[Interactions](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Timestamp] [datetimeoffset](7) NOT NULL,
[PersonId] [bigint] NOT NULL,
[ThingId] [bigint] NOT NULL,
CONSTRAINT [PK_Interactions] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
CREATE TABLE [dbo].[Persons](
[Id] [bigint] NOT NULL,
[FullName] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
CREATE TABLE [dbo].[Things](
[Id] [bigint] NOT NULL,
[Type] [int] NOT NULL,
[BaseField] [int] NOT NULL,
[IntermediateField] [int] NULL,
[ConcreteField] [int] NULL,
CONSTRAINT [PK_Things] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
INSERT [dbo].[Persons] ([Id], [FullName]) VALUES (1, N'Jane Doe')
INSERT [dbo].[Persons] ([Id], [FullName]) VALUES (2, N'John Doe')
GO
INSERT [dbo].[Things] ([Id], [Type], [BaseField], [IntermediateField], [ConcreteField]) VALUES (1, 1, 11, NULL, NULL)
INSERT [dbo].[Things] ([Id], [Type], [BaseField], [IntermediateField], [ConcreteField]) VALUES (2, 2, 22, NULL, 2002)
INSERT [dbo].[Things] ([Id], [Type], [BaseField], [IntermediateField], [ConcreteField]) VALUES (3, 101, 33, 303, 3003)
INSERT [dbo].[Things] ([Id], [Type], [BaseField], [IntermediateField], [ConcreteField]) VALUES (4, 102, 44, 404, NULL)
GO
INSERT [dbo].[Interactions] ([Timestamp], [PersonId], [ThingId]) VALUES (SYSDATETIMEOFFSET(), 1, 1)
INSERT [dbo].[Interactions] ([Timestamp], [PersonId], [ThingId]) VALUES (SYSDATETIMEOFFSET(), 1, 2)
INSERT [dbo].[Interactions] ([Timestamp], [PersonId], [ThingId]) VALUES (SYSDATETIMEOFFSET(), 1, 3)
INSERT [dbo].[Interactions] ([Timestamp], [PersonId], [ThingId]) VALUES (SYSDATETIMEOFFSET(), 1, 4)
INSERT [dbo].[Interactions] ([Timestamp], [PersonId], [ThingId]) VALUES (SYSDATETIMEOFFSET(), 2, 1)
INSERT [dbo].[Interactions] ([Timestamp], [PersonId], [ThingId]) VALUES (SYSDATETIMEOFFSET(), 2, 2)
INSERT [dbo].[Interactions] ([Timestamp], [PersonId], [ThingId]) VALUES (SYSDATETIMEOFFSET(), 2, 3)
INSERT [dbo].[Interactions] ([Timestamp], [PersonId], [ThingId]) VALUES (SYSDATETIMEOFFSET(), 2, 4)
GO
ALTER TABLE [dbo].[Interactions] WITH CHECK ADD CONSTRAINT [FK_Interactions_Persons] FOREIGN KEY([PersonId])
REFERENCES [dbo].[Persons] ([Id])
GO
ALTER TABLE [dbo].[Interactions] CHECK CONSTRAINT [FK_Interactions_Persons]
GO
ALTER TABLE [dbo].[Interactions] WITH CHECK ADD CONSTRAINT [FK_Interactions_Things] FOREIGN KEY([ThingId])
REFERENCES [dbo].[Things] ([Id])
GO
ALTER TABLE [dbo].[Interactions] CHECK CONSTRAINT [FK_Interactions_Things]
GO
I feel that, since the WhereIntermediateThings variant works with explicit casting afterwards, that there may be hope to tweak the WhereSelectIntermediateThings or WhereCastIntermediateThings variants to resolve their issues without having to resort to explicit casting afterwards. However, I've run out of ideas.
The plain GetTable<IntermediateThing>() and GetTable<BaseThing>().OfType<IntermediateThing>() variant work identically. From my understanding they are the methods that should be used but for some reason don't work. Thus I'm thinking it's because either I'm potentially missing something to provide Linq2Db the necessary information or is a bug in Linq2Db. I've searched the Linq2Db documentation site and looked at unit tests specific to inheritance to no avail so far.