Wednesday, June 22, 2011

Mapping class to a database view with Entity Framework. EntityType 'ClassHere' has no key defined.

You shall receive this kind of error if you map your class to a view if your class has no designated primary key:

One or more validation errors were detected during model generation:

System.Data.Edm.EdmEntityType: : EntityType 'SalesOnEachCountry' has no key defined. Define the key for this EntityType.
System.Data.Edm.EdmEntitySet: EntityType: The EntitySet SalesOnEachCountryList is based on type SalesOnEachCountry that has no keys defined.

Data source:
create table Country
(
CountryId int identity(1,1) not null primary key,
CountryCode varchar(4) not null unique,
CountryName varchar(30) not null unique
);
 
 
 
insert into Country(CountryCode,CountryName)
values('PH','Philippines');
 
insert into Country(CountryCode,CountryName)
values('CN','China');
 
insert into Country(CountryCode,CountryName)
values('JP','Japan');
 
 
create table City
(
CityId int identity(1,1) not null primary key,
CityCode varchar(3) not null unique,
CityName varchar(30) not null unique,
CountryId int not null references Country(CountryId)
);

 
insert into City(CityCode, CityName, CountryId)
values('MKT','Makati',1);
 
insert into City(CityCode, CityName, CountryId)
values('MNL','Manila',1);
 
 
insert into City(CityCode, CityName, CountryId)
values('BEI','Beijing',2);
 
insert into City(CityCode, CityName, CountryId)
values('SHA','Shanghai',2);
 
 
insert into City(CityCode, CityName, CountryId)
values('TKY','Tokyo',3);
 
insert into City(CityCode, CityName, CountryId)
values('KYT','Kyoto',3);

create table Sales
(
OrNo int identity(1,1) not null primary key,
OrYear int not null,
CityId int not null references City(CityId),
OrAmount numeric(18,6) not null
);

declare @i int;
 
set @i = 0;
while @i < 5 begin
 insert into Sales(OrYear,CityId,OrAmount) values(2010,1,2);
 set @i = @i + 1;
end;
 
set @i = 0;
while @i < 5 begin
 insert into Sales(OrYear,CityId,OrAmount) values(2010,2,2);
 set @i = @i + 1;
end;

set @i = 0;
while @i < 10 begin
 insert into Sales(OrYear,CityId,OrAmount) values(2011,1,2);
 set @i = @i + 1;
end;
 
set @i = 0;
while @i < 10 begin
 insert into Sales(OrYear,CityId,OrAmount) values(2011,2,2);
 set @i = @i + 1;
end;
 

create view SalesOnEachCountry -- with SchemaBinding
as
select x.CountryId, x.CountryName, s.OrYear, 
COUNT_BIG(*) AS SalesCount, sum(s.OrAmount) as TotalSales
from dbo.Sales s
join dbo.City c on c.CityId = s.CityId
join dbo.Country x on c.CountryId = x.CountryId
group by x.CountryId, s.OrYear
    ,x.CountryName -- ancillary field(s)
-- CREATE UNIQUE CLUSTERED INDEX ux_SalesOnEachCountry on SalesOnEachCountry(CountryId, OrYear);


Ancillary fields on GROUP BY

select * from SalesOnEachCountry;


CountryId   CountryName                    OrYear      SalesCount TotalSales
----------- ------------------------------ ----------- ---------- -------------
1           Philippines                    2010        10         20.000000
1           Philippines                    2011        20         40.000000

(2 row(s) affected)



You must put a primary key on your class. Howevery, if you just put the primary key on CountryId for the following mapping...

public class SalesOnEachCountry
{        
    [Key]
    public int CountryId { get; set; }
    public string CountryName { get; set; }        
    public int OrYear { get; set; }
    public long SalesCount { get; set; }
    public decimal TotalSales { get; set; }
}

..., even there's no runtime error for this code...
foreach (SalesOnEachCountry s in db.SalesOnEachCountryList)
{
    Console.WriteLine("{0} {1} {2}", s.CountryName, s.OrYear, s.TotalSales);
}

...,Entity Framework will still function normally and produces this incorrect output...
Philippines 2010 20.000000
Philippines 2010 20.000000

You must put the unique combination on Country and Year, remove the Key attribute on SalesOnEachCountry class, and move it on OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

    modelBuilder.Entity<SalesOnEachCountry>().HasKey(x => new { x.CountryId, x.OrYear });     
}

Now that will produce correct result:
Philippines 2010 20.000000
Philippines 2011 40.000000

Another point, though you can opt to use attribute approach on designating composite primary key on the class itself, the intent of composite key is more clear and intuitive if it is placed on OnModelCreating. As for those who preferred to indicate composite primary key by using attributes, here's how it is done (remove first the HasKey code from OnModelCreating):

public class SalesOnEachCountry
{        
    [Key, Column(Order=0)] public int CountryId { get; set; }
    public string CountryName { get; set; }
    [Key, Column(Order=1)] public int OrYear { get; set; }
    
    public long SalesCount { get; set; }      
    public decimal TotalSales { get; set; }
}

No comments:

Post a Comment