CODEWITHSUNDEEP
c#.netsql-serverlinq
mykhailovskyi
mykhailovskyi

Reputation: 680

How to add field not mapped to table in Linq to Sql

In Entity Framework I can apply NotMapped attribute to a property which I do NOT want to create a column in a database table for. How to get the same effect for auto generated classes in DBML file? I have a StoredProcedure that returns some additional fields. I called SP like:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name = "dbo.sp_GetSupplierArticles")]
public ISingleResult<SupplierArticle> GetSupplierArticles([global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "BigInt")] long mainArticleId, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "BigInt")] long? userId)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), mainArticleId, userId);
    return ((ISingleResult<SupplierArticle>)(result.ReturnValue));
}

Necessary field I added into the separated partial class. Without any additional attributes it returns default value for my and applied [Column(IsDbGenerated = false)] in the separated partial class:

public partial class SupplierArticle
{
    [Column(IsDbGenerated = false)]
    public double Extra { get; set; }
}

So it works until I try to get SupplierArticle using another query (not my stored procedure):

db.LoadOptions = db.GenerateDataLoadOptions(entitiesToInclude);
var query =
    from shoppingCartItem in db.ShoppingCartItems
    where shoppingCartItem.UserId == userId
    select shoppingCartItem;
return query.ToList();

My entity is loaded due to LoadOptions (passed in entitiesToInclude parameter). In this query and another which try to load "poor" entity with properties that defined in .dbml file I get exception: Invalid column name 'Extra' and the same message for each additional property.

What is the proper way to extend entity or how to avoid that exception?

UPD: If I remove all attributes exception no longer occurs. But added properties are not initialized when SP returns a result.

Upvotes: 15

Views: 2909

Answers (2)

djones
djones

Reputation: 2393

I would suggest creating a complex type for that stored procedure. I would even go as far as creating complex types for all of your stored procedures as this is best practice. You can then add an extension method, or a method to your partial classes that will convert the complex type returned from the stored procedure to it's related entity, and vice versa. Another option would be to include a foreign key to your complex stored procedure type, and a navigation property pointing to the correct entity.

These are, of course, solutions to a problem that EF itself doesn't address. This is expected as EF is an ORM and is not concerned with what's not persisted.

Upvotes: 2

RannyMeier
RannyMeier

Reputation: 75

A SQL View could be used if you wanted to. The View is composible and LINQ to SQL does not distinguish it from a Table. First rename the original table. Then make a View with the same name as the original table, while including the extra column with a default value. Let the Stored Procedure use the table with the new name. Of course any other SQL objects with references to the table need to be updated with the new table name. Now both LINQ to SQL and the SP will be happy.

Upvotes: 0

Related Questions