0

In a data model like this (http://alanstorm.com/2009/img/magento-book/eav.png) I want to get the value from an EAV_Attribute using Linq to SQL.

enter image description here

Assuming that an EAV_Attribute only exists in one inherited table (varchar, decimal, int, etc.) how can I get it in a linq query?

I know that I can use the Inheritance for this, but I want to execute it in the SQL Database side...

Is it possible to do a kind of Coalesce in Linq, considering that the elements have different types?

Danny Varod
  • 17,324
  • 5
  • 69
  • 111
Gui Ferreira
  • 4,367
  • 6
  • 28
  • 41
  • Assuming you're using C# to build your LINQ to SQL query, you can use the null coalesce operator (??) with outer joins. Give it a shot! – Nick Vaccaro Dec 04 '12 at 17:38
  • I've already tried that, but I can't do it because "Value" property has a different data type in each table. – Gui Ferreira Dec 04 '12 at 17:52
  • Related: http://stackoverflow.com/a/13673607 – Danny Varod Dec 05 '12 at 22:09
  • If you are using Entity Framework then you are using LINQ to Entities, not LINQ to SQL (a different technology that Microsoft declared end of life). – Danny Varod Dec 05 '12 at 22:32
  • Also, what did you mean by "I know that I can use inheritance for this" and why do you think that would prevent the query from being translated to SQL? – Danny Varod Dec 05 '12 at 22:36

2 Answers2

0

EAV and linq is not a happy marriage. I think your best shot is to create an unmapped property in eav_attribute that resolves the value (as object) from it's typed attribute child. With entity framework, you won't be able to use this property in an expression (i.e. not in a Where or Select), You must convert to IEnumerable first to access it. (Linq-to-sql may allow it because it can switch to linq-to-objects below the hood).

Another option is to create a calculated column of type sql_variant that does the same, but now in t-sql code. But... EF does not suport sql_variant. You've got to use some trickery to read it.

That's the reading part.

For setting/modifying/deleting values I don't see any shortcuts. You just have to handle the objects as any object graph with parents and children. In sql server you can't use cascaded delete because it can only be defined for one foreign key. (This may tackle that, but I never tried).

So, not really good news, I'm afraid. Maybe good to know that in one project I also work with a database that has an inevitable EAV part. We do it with EF too, but it's not without friction.

Community
  • 1
  • 1
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
0

First of all, I recommend using TPH and not TPT for EAV tables. (One table with multiple nullable value columns (one per type) + discriminator vs. one table per type.)

Either way, if you modelled the value entity as an abstract class (containing the two IDs) with an inheriting entity per value data type that adds the value property, then your LINQ should look like this:

var valueEntity = context.ProductAttributes.Where(pa =>
        pa.ProductId == selectedProductId
        && pa.AttributeTypeId == selectedAttributeTypeId)
    .SingleOrDefault() as ProductAttributeOfDouble;

if valueEntity != null
    return valueEntity.Value;

return null;

Where the entity types are: Product, AttributeType, ProductAttribute, ProductAttributeOfDouble, ... ProductAttributeOfString.

Danny Varod
  • 17,324
  • 5
  • 69
  • 111