Archive for category Linq

Entity Framework 4.1 Code First (With One to Many relationship) Code Example

A quick piece of code demo’ing a very common/ simple usage scenario- basic one to many with some demo data being seeded into the database; I thought this might be needed as I’ve waded through a LOT of exmaples for various beta versions and different releases of EF, but had little luck actually finding something relevant. The best site I came across was tucked away on the ADO.net team’s blog- Using DbContext in EF 4.1. First– the entities and the database context

    public class Category
    {
        [Key]
        public Guid ID { get; set; }

        public string Title { get; set; }
        public virtual ICollection<Product> Products { get; set; }  
                   //virtual makes this lazily loaded

        public Category()
        {
            this.ID = Guid.NewGuid();
        }
    }

    public class Product
    {
        [Key]
        public Guid ID { get; set; }

        public string Title { get; set; }
        public string Details { get; set; }
        public double Price { get; set; }
        public string ImageFileName { get; set; }

        public DateTime DateAdded { get; set; }

        public virtual Category Category { get; set; }  
                   //virtual makes this lazily loaded

        public Product()
        {
            this.ID = Guid.NewGuid();
        }
    }

    public class MyDBContext: DbContext
    {
        public DbSet<Product> Products { get; set; }
        public DbSet<Category> Categories { get; set; }
    }

Note the use of ICollection on Products in Category, and the use of DbSet in the context. I used a DbSet in lieu of ICollection on the Products entity and found it went mental if I had a Product with no category (something which I wanted to be valid) – swapping it to ICollection fixed this issue (I don’t know enough of the inner workings to explain why)

BAMM! Next for the seeding- add a class like so;

    public class MyDBInitializer : DropCreateDatabaseAlways<MyDBContext>
    {
        protected override void Seed(Entities.ComicolleDB context)
        {
            Category marvel = new Category()
            {
                Title = "Category 1"
            };
            Category dc = new Category()
            {
                Title = "Category 2"
            };

            context.Categories.Add(marvel);
            context.Categories.Add(dc);

            context.Products.Add(new Product()
            {
                Title = "Test1",
                ImageFileName = "image-1.gif",
                Details = "This is a test",
                Price = 4.99,
                DateAdded = DateTime.Now,
                Category = marvel
            });

            context.Products.Add(new Product()
            {
                Title = "Test2",
                ImageFileName = "image-1.gif",
                Details = "This is a test",
                Price = 4.99,
                DateAdded = DateTime.Now,
                Category = dc
            });

            context.Products.Add(new Product()  // no category
            {
                Title = "Test3",
                ImageFileName = "image-1.gif",
                Details = "This is a test",
                Price = 4.99,
                DateAdded = DateTime.Now
            });

            context.SaveChanges();
        }
    }

Then add the following line to the end of your Application_Start() method in global.asax;

Database.SetInitializer<MyDBContext>(new MyDBInitializer());

Voila- you can then go ahead and use your model in whatever applications you see fit- here are some example;

MyDBContext db = new MyDBContext();

// get a category, and make sure the products are loaded
Category the_category = db.Categories.Include("Products")
                           .Where(x => x.Title == "Category 1").SingleOrDefault();

// get all the products for that category
List<Product> some_products = the_category.Products;

// get all the categories (without loading the products)
List<Category> some_categories = db.Categories.ToList();

1 Comment

Entity Framework “The data reader is incompatible with the specified complex type.”

I recently had this problem with a site I’m working on. I’m using Entity framework to access a bunch of legacy stored procedures, originally in MySQL but ported over to MSSQL. Having added the sprocs to EF and let it auto-generate it’s magic, I was shocked to find one of my calls throwing the following exception;

System.Data.EntityCommandExecutionException: The data reader is incompatible with the specified &#39;ChinookModel.GetVehicleDescFromModelId_Result1&#39;. A member of the type, &#39;ivaluationid1&#39;, does not have a corresponding column in the data reader with the same name.
   at System.Data.Query.InternalTrees.ColumnMapFactory.GetMemberOrdinalFromReader(DbDataReader storeDataReader, EdmMember member, EdmType currentType, Dictionary`2 renameList)
   at System.Data.Query.InternalTrees.ColumnMapFactory.GetColumnMapsForType(DbDataReader storeDataReader, EdmType edmType, Dictionary`2 renameList)
   at System.Data.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary`2 renameList)
   at System.Data.Query.InternalTrees.ColumnMapFactory.CreateFunctionImportStructuralTypeColumnMap(DbDataReader storeDataReader, FunctionImportMapping mapping, EntitySet entitySet, StructuralType baseStructuralType)
   at System.Data.EntityClient.EntityCommandDefinition.FunctionColumnMapGenerator.System.Data.EntityClient.EntityCommandDefinition.IColumnMapGenerator.CreateColumnMap(DbDataReader reader)
   at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption)
   at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
   at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)
   at Chinook.Model.ChinookEntities.GetVehicleDescFromModelId(Nullable`1 modelid) in C:\_Dev\Chinook\Source\Chinook.Model\Chinook.Designer.cs:line 160
   at Chinook.Model.GlassGuideDataProvider.GetVehicleFromModelId(Int32 ModelId) in C:\_Dev\Chinook\Source\Chinook.Model\GlassGuideDataProvider.cs:line 104
   at Chinook.Services.VehicleDataLookup.GetVehicleForRegistration(String reg) in C:\_Dev\Chinook\Source\Chinook\Services\VehicleDataLookup.asmx.cs:line 81

It seemed to be moaning that one of the columns in the complex type I set for it to use as the return type, doesn’t match to a column actually being returned, despite entity framework having auto generated that type itself! The cause was, as is generally the case, user error. Inspecting the sproc a bit closer I noticed one of the columns was being returned twice, using the same name. EF generated me a call with an iValuation and iValuation1 column, but failed to match iValuation1 because in reality both columns were simply called iValuation! So a simple, and kind of obvious fix, but worth noting to stop someone else loose a little more hair when they’re confonted with this error!

1 Comment

Linq Distinct!

I have a List of books- each book has a category – i want to find out all the unique categories involved, given a list of books- i knew i could do this with linq, but not quite sure how!

It turned out to be easy..

List<Category> categories = _books.Select(i => i.Category).Distinct();

There was a minor complication however- Distinct obviously needs to be able to compare different instances of Category- you can supply a delegate to a custom EquityComparer class- otherwise it will simply check if this is the same instance of the object, which will (probably) always come back false rendering your distinct useless. So i created this;

    public class CategoryComparer : IEqualityComparer<Category>
    {
        public bool Equals(Category x, Category y)
        {
            return x.Id == y.Id;
        }

        public int GetHashCode(Category obj)
        {
            return obj.Id.GetHashCode();
        }
    }

then updated my linq to use it..

List<Category> categories = _books.Select(i => i.Category).Distinct(new CategoryComparer());

No Comments

Reading & Writing XML Files with LINQ

LINQ to XML Quick Brain Dump – Garry Pilkington.

No Comments