Creating Model from Database, not creating complex primary keys
Posted: Fri 30 Aug 2013 09:58
I have several tables that have more than one field involved in the primary key, it seems after a recent update that Entity Developer only marks the first key of the table as the primary key. This is cause a lot of problems with queries, for example this query
returns the correct number of items, but all items are populated with the values from the first row only!
dotConnect for SQLite Version: 4.6.301.0
Server Version: 3.7.17
PRAGMA synchronous=NORMAL
PRAGMA journal_mode=WAL
Example table:
Code: Select all
var query = from r in _table
where r.CountryId == item.RowData.CountryId
&& r.GDPType == item.RowData.GDPType
&& r.CurrencyCode == item.RowData.CurrencyCode
select r;
dotConnect for SQLite Version: 4.6.301.0
Server Version: 3.7.17
PRAGMA synchronous=NORMAL
PRAGMA journal_mode=WAL
Example table:
Code: Select all
CREATE TABLE GDPData (
CountryId smallint NOT NULL,
GDPType smallint NOT NULL,
Year smallint NOT NULL,
CurrencyCode char(3) NOT NULL,
Value numeric(50,6) DEFAULT NULL,
Source nvarchar(100) DEFAULT NULL,
UserDefined boolean NOT NULL DEFAULT 0,
PrevBatchIds nvarchar(50) DEFAULT NULL,
BatchId integer DEFAULT NULL,
PRIMARY KEY (CountryId, GDPType, Year, CurrencyCode),
/* Foreign keys */
FOREIGN KEY (CountryId)
REFERENCES Country(Id)
ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY (CurrencyCode)
REFERENCES Currency(Code)
ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY (GDPType)
REFERENCES GDPType(Id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
CREATE INDEX GDPDataIdx_Country
ON GDPData
(CountryId);
CREATE INDEX GDPDataIdx_CurrencyCode
ON GDPData
(CurrencyCode);
CREATE INDEX GDPDataIdx_CurrencyType
ON GDPData
(CurrencyCode, GDPType, CountryId, Year);
CREATE INDEX GDPDataIdx_Type
ON GDPData
(GDPType);
Example Data:
INSERT INTO GDPData (CountryId, GDPType, Year, CurrencyCode, Value, Source, UserDefined, PrevBatchIds, BatchId) VALUES
(4, 1, 1960, 'USD', 1210000077, 'NULL', '1', 'NULL|4', 8),
(4, 1, 1961, 'USD', 1235000013, 'NULL', '1', 'NULL|4', 8),
(4, 1, 1962, 'USD', 1230000026, 'NULL', '1', 'NULL|4', 8),
(4, 1, 1963, 'USD', NULL, 'NULL', '1', 'NULL|4', 8),
(4, 1, 1964, 'USD', 800000045.5, 'NULL', '1', 'NULL|4', 8);