Why do I get different values from my EntitySet depending on how I LINQ to it?

Question

In debugging the issue in this thread: InvalidCastException when querying nested collection with LINQ I found out that something is wrong with how my Category EntitySet is populated. After selecteding a Category and throwing this exception to see what's going on I get this:

throw new Exception("CID: " + cat.CategoryID +
  " LCID: "        + cat.LocalizedCategories.First().LocalizedCategoryID +
  " CID from LC: " + cat.LocalizedCategories.First().Category.CategoryID);

CID: 352 LCID: 352 CID from LC: 191

What am I doing wrong that causes CategoryID to have different values depending on how I LINQ to it? It should be 191, and not the same value as the LocalizedCategoryID.

This is the code I use to get the Category:

int categoryId = 352; // In reality this comes from a parameter and is supposed
                      // to be 191 to get the Category.
var cat = categoriesRepository.Categories.First(c => c.CategoryID == categoryId);

This is my domain object with some unrelated stuff stripped:

[Table(Name = "products")]
public class Product
{
    [HiddenInput(DisplayValue = false)]
    [Column(Name = "id", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public int ProductID { get; set; }

    [Required(ErrorMessage = "Please enter a product name")]
    [Column]
    public string Name { get; set; }

    [Required(ErrorMessage = "Please enter a description")]
    [DataType(DataType.MultilineText)]
    [Column(Name = "info")]
    public string Description { get; set; }

    private EntitySet<Category> _Categories = new EntitySet<Category>();
    [System.Data.Linq.Mapping.Association(Storage = "_Categories", OtherKey = "CategoryID")]
    public ICollection<Category> Categories
    {
        get { return _Categories; }
        set { _Categories.Assign(value); }
    }
}

[Table(Name = "products_types")]
public class Category
{
    [HiddenInput(DisplayValue = false)]
    [Column(Name = "id", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public int CategoryID { get; set; }

    public string NameByCountryId(int countryId)
    {
        return _LocalizedCategories.Single(lc => lc.CountryID == countryId).Name;
    }

    private EntitySet<LocalizedCategory> _LocalizedCategories = new EntitySet<LocalizedCategory>();
    [System.Data.Linq.Mapping.Association(Storage = "_LocalizedCategories", OtherKey = "LocalizedCategoryID")]
    public ICollection<LocalizedCategory> LocalizedCategories
    {
        get { return _LocalizedCategories; }
        set { _LocalizedCategories.Assign(value); }
    }

    private EntitySet<Product> _Products = new EntitySet<Product>();
    [System.Data.Linq.Mapping.Association(Storage = "_Products", OtherKey = "ProductID")]
    public ICollection<Product> Products
    {
        get { return _Products; }
        set { _Products.Assign(value); }
    }
}

[Table(Name = "products_types_localized")]
public class LocalizedCategory
{
    [HiddenInput(DisplayValue = false)]
    [Column(Name = "id", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public int LocalizedCategoryID { get; set; }

    [Column(Name = "products_types_id")]
    private int CategoryID;
    private EntityRef<Category> _Category = new EntityRef<Category>();
    [System.Data.Linq.Mapping.Association(Storage = "_Category", ThisKey = "CategoryID")]
    public Category Category
    {
        get { return _Category.Entity; }
        set { _Category.Entity = value; }
    }

    [Column(Name = "country_id")]
    public int CountryID { get; set; }

    [Column]
    public string Name { get; set; }
}

score 1 · Accepted Answer · answered Sep 24 '12 at 19:54

1

This (in class Category) looks weird:

[System.Data.Linq.Mapping.Association(Storage = "_LocalizedCategories",
    OtherKey = "LocalizedCategoryID" )] // ????
public ICollection<LocalizedCategory> LocalizedCategories

Category has a collection of LocalizedCategorys, which means that in the database the table products_types_localized has a foreign keyCategoryID. That field should be the "OtherKey". How was this mapping generated?

answered Sep 24 '12 at 19:54

Gert Arnold

105,341
31
202
291

That solved it, big thanks! The mappings have been manually coded by me. Partly because I haven't learned enough about automatic ways to do it (tried installing Entity Framework Power Tools Beta 2 but it didn't want to install). Partly because the database is poorly designed and has no foreign keys. This is something I want to correct later, but don't want to do now because I don't want to change the structure before we move from our old Web Forms 1.0 website to the new MVC4 I'm building. – Daniel Flöijer Sep 24 '12 at 20:48
Ah, that's why. BTW, I think there is a similar thing in `Product.Categories`. Well, I guess you started reviewing it already. – Gert Arnold Sep 24 '12 at 20:50
Yes, good catch! I think the reason I misunderstood is that there isn't even a real reference from Product to Category in the database, so there was nothing to map. Thus I picked the wrong key because I thought it could be done. I guess I'll have to do some select statements instead or something. I will look into it. Later I guess a M:M table might be a good option though. – Daniel Flöijer Sep 25 '12 at 05:46
My bad, I do have a M:M table. I've now rewritten the code to use it and things are starting to look real smooth :-) – Daniel Flöijer Sep 25 '12 at 14:07

Why do I get different values from my EntitySet depending on how I LINQ to it?

1 Answers1

Linked