LINQ to SQL - mapping exception when using abstrac

Question

Problem: I would like to share code between multiple assemblies. This shared code will need to work with LINQ to SQL-mapped classes.

I've encountered the same issue found here, but I've also found a work-around that I find troubling (I'm not going so far as to say "bug").

All the following code can be downloaded in this solution.

Given this table:

create table Users
(
      Id int identity(1,1) not null constraint PK_Users primary key
    , Name nvarchar(40) not null
    , Email nvarchar(100) not null
)

and this DBML mapping:

<Table Name="dbo.Users" Member="Users">
  <Type Name="User">
    <Column Name="Id" Modifier="Override" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
    <Column Name="Name" Modifier="Override" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
    <Column Name="Email" Modifier="Override" Type="System.String" DbType="NVarChar(100) NOT NULL" CanBeNull="false" />
  </Type>
</Table>

I've created the following base classes in one assembly "Shared":

namespace TestLinq2Sql.Shared
{
    public abstract class UserBase
    {
        public abstract int Id { get; set; }
        public abstract string Name { get; set; }
        public abstract string Email { get; set; }
    }

    public abstract class UserBase<TUser> : UserBase where TUser : UserBase
    {
        public static TUser FindByName_Broken(DataContext db, string name)
        {
            return db.GetTable<TUser>().FirstOrDefault(u => u.Name == name);
        }

        public static TUser FindByName_Works(DataContext db, string name)
        {
            return db.GetTable<TUser>().FirstOrDefault(u => u.Name == name && 1 == 1);
        }

        public static TUser FindByNameEmail_Works(DataContext db, string name, string email)
        {
            return db.GetTable<TUser>().FirstOrDefault(u => u.Name == name || u.Email == email);
        }
    }
}

These classes are referenced in another assembly "Main", like so:

namespace TestLinq2Sql
{
    partial class User : TestLinq2Sql.Shared.UserBase<User>
    {

    }
}

The DBML file is located in the "Main" assembly, as well.

When calling User.FindByName_Broken(db, "test"), an exception is thrown:

System.InvalidOperationException: Class member UserBase.Name is unmapped.

However, the other two base static methods work.

Furthermore, the SQL generated by calling User.FindByName_Works(db, "test") is what we were hoping for in the broken call:

SELECT TOP (1) [t0].[Id], [t0].[Name], [t0].[Email]
FROM [dbo].[Users] AS [t0]
WHERE [t0].[Name] = @p0
-- @p0: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [test]

While I am willing to use this 1 == 1 "hack" for single predicate queries, is there a better way of sharing LINQ to SQL-aware code in a base/shared/core assembly?

Answer 1

Try including OfType before Where clause

return _dbContext.GetTable<T>().OfType<T>().Where(expression).ToList();

Answer 2

You're asking several questions here Jarrod, can you be more specific? That is, do you just want to know why your method fails? Or maybe you want a way of using data objects across different projects? I'm assuming you're not trying to use LINQ to SQL as a database mapping layer and that you are using it as a domain model? In which case, do both applications implement the same domain (business processes, validation, etc.)?

Answer 3

I have encountered this problem many times in the past because we have a similar architecture in a framework that we use in our company. You may have noticed that if you use the declarative style LINQ queries you'll not encounter this problem. For example the following code will work:

return (from i in db.GetTable<TUser>() where i.Name = "Something").FirstOrDefault();

However, since we are using dynamic filter expressions we couldn't use this method. The alternative solution is to use something like this:

return db.GetTable<TUser>().Select(i => i).Where(i => i.Name == "Something").SingleOrDefault();

This solution solved our problem since we can inject a ".Select(i => i)" to the beginning of almost all expressions. This will cause the Linq engine not to look at the base class for the mappings and will force it to look at the actual entity class and find the mappings.

Hope it helps

Answer 4

I've had luck defining data classes in a shared assembly and consuming them in many assemblies versus mapping many assemblies' data classes to a shared contract. Using your example namespaces, put a custom DataContext and your shared data classes in TestLinq2Sql.Shared:

namespace TestLinq2Sql.Shared
{
    public class SharedContext : DataContext
    {
        public Table<User> Users;
        public SharedContext (string connectionString) : base(connectionString) { }
    }

    [Table(Name = "Users")]
    public class User
    {
        [Column(DbType = "Int NOT NULL IDENTITY", IsPrimaryKey=true, CanBeNull = false)]
        public int Id { get; set; }

        [Column(DbType = "nvarchar(40)", CanBeNull = false)]
        public string Name { get; set; }

        [Column(DbType = "nvarchar(100)", CanBeNull = false)]
        public string Email { get; set; }
    }
}

Then consume the DataContext from any other assembly:

using (TestLinq2Sql.Shared.SharedContext shared = 
    new TestLinq2Sql.Shared.SharedContext(
        ConfigurationManager.ConnectionStrings["myConnString"].ConnectionString))
{
    var user = shared.Users.FirstOrDefault(u => u.Name == "test");
}  

Answer 5

This looks like a bug - we special case Single on a primary key to do a local lookup but it looks like this code path is not grabbing the metadata properly.

The 1=1 hack will mean it goes via a normal database round-trip but really a bug should be filed...