LINQ, entity that implements Interface and exception in mapping

Question

I'm using repository pattern with LINQ, have IRepository.DeleteOnSubmit(T Entity). It works fine, but when my entity class has interface, like this:

public interface IEntity { int ID {get;set;} }

public partial class MyEntity: IEntity {

    public int ID { 
        get { return this.IDfield; }
        set { this.IDfield=value;  }
    }
}

and then trying to delete some entity like this:

IEntity ie=repository.GetByID(1);
repoitory.DeleteOnSubmit(ie);

throws
The member 'IEntity.ID' has no supported translation to SQL.

fetching data from DB works, but delete and insert doesn't. How to use interface against DataContext?

Answer 1

Try this:

using System.Data.Linq.Mapping;

public partial class MyEntity: IEntity 
 {    [Column(Storage="IDfield", DbType="int not null", IsPrimaryKey=true)]
      public int ID 
       {         
          get { return this.IDfield; }        
          set { this.IDfield=value;  }    
       } 
 }

Answer 2

What of you use the ColumnAttribute on the interface member instead?

public interface IEntity  
   { [Column(Storage="IDfield", DbType="int not null", IsPrimaryKey=true)]
     int ID {get;set;} 
   }

Answer 3

I think that problem is in table. It's many-to-many relations table, with two fields for joining (db shema like this: Tags - tagArticleConn - Articles), which are also both primary fields.
dataContext.GetTable(T).DeleteOnSubmit(IEntity T) get confused when trying to find primary field in interface/entity that has to be deleted.

Do you maybe have some other solution for this problem:

in DB i have Article, Event, Media, Photo, which are content of my App. Every content has to have Tags, so i have connection tables TagArticle, TagEvent,..., which connects content (Article for ex.) with Tag table.
How to make one class that handels saving tags, for example:

tagManager.SaveTags<T>(string[] tags, 
    IRepository<Tag> tagsRepository, 
    IRepository<T> ContentRepository, 
    int idOfContentEntity) where T:class, ITagConnectionTable,new()

hope you're get the picture.
For now I have 5 SaveTags() methods, for every content type (Article, Event, ...)

Answer 4

Can you post the stack trace for the "no supported translation to SQL" exception?

Answer 5

Here it is:
Exception message: The member 'MMRI.DAL.ITag.idContent' has no supported translation to SQL.

Code:

var d = repContent.GetAll().Where(x => x.idContent.Equals(idContent));
foreach (var tagConnect in d)    <- error line
{
    repContet.DeleteOnSubmit(tagConnect);

(it gets all tags from DB, and deletes them)

And stack trace:

[NotSupportedException: The member 'MMRI.DAL.ITag.idContent' has no supported translation to SQL.]
   System.Data.Linq.SqlClient.Visitor.VisitMember(SqlMember m) +621763
   System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) +541
   System.Data.Linq.SqlClient.SqlVisitor.VisitExpression(SqlExpression exp) +8
   System.Data.Linq.SqlClient.SqlVisitor.VisitBinaryOperator(SqlBinary bo) +18
   System.Data.Linq.SqlClient.Visitor.VisitBinaryOperator(SqlBinary bo) +18
   System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) +196
   System.Data.Linq.SqlClient.SqlVisitor.VisitExpression(SqlExpression exp) +8
   System.Data.Linq.SqlClient.SqlVisitor.VisitSelectCore(SqlSelect select) +46
   System.Data.Linq.SqlClient.Visitor.VisitSelect(SqlSelect select) +20
   System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) +1024
   System.Data.Linq.SqlClient.SqlProvider.BuildQuery( ...

When I try do decorate partial class:

[Column(Storage = "_idEvent", DbType = "Int NOT NULL", IsPrimaryKey = true)]
public int idContent
{ get { return this.idEvent; } set { this.idEvent=value; } }

it throws error "Invalid column name 'idContent'."

Answer 6

This works for me -

public partial class MyEntity: IEntity 
 {    [Column(Name = "IDfield", Storage = "_IDfield", IsDbGenerated = true)]
      public int ID 
       {         
          get { return this.IDfield; }        
          set { this.IDfield=value;  }    
       } 
 }

Answer 7

For translating your LINQ query to actual SQL, Linq2SQL inspects the expression you give it. The problem is that you have not supplied enough information for L2S to be able to translate the "ID" property to the actual DB column name. You can achieve what you want by making sure that L2S can map "ID" to "IDField".

This should be possible using the approach provided in answers.

If you use the designer, you can also simply rename the class property "IDField" to "ID", with the added benefit that you won't have to explicitly implement the "ID" property in your partial class anymore, i.e. the partial class definition for MyEntity simply becomes:

public partial class MyEntity: IEntity 
{    
}