LINQ Query returns proper count but duplicates data
up vote
0
down vote
favorite
I have the below query which brings the position value.
If I run the SQL query obtained from the debug window the records look okay.
But the list doesn't seem to duplicate the value for Report Date field in c#.
var query = (from AceBi in context.AceBiMonthlyDaos
join Inst in context.Instruments on AceBi.Naid equals Inst.AceId
join FinAcct in context.FinancialAccounts on AceBi.PositionAccountKey equals
FinAcct.AceClientId where AceBi.PositionAccountKey != null
join Invst in context.Investments
on new a = Inst.Id, b = FinAcct.Id equals new a = Invst.InstrumentId, b = Invst.FinAcctId
select new AceBi, Invst).Distinct();
Output from SQL image 1
Output list in Entity image 2
linq-to-sql linq-to-entities
edited Nov 11 at 8:12
Mohammad
13.1k93158
asked Nov 11 at 1:14
SPG
113
add a comment |
up vote
0
down vote
favorite
I have the below query which brings the position value.
If I run the SQL query obtained from the debug window the records look okay.
But the list doesn't seem to duplicate the value for Report Date field in c#.
var query = (from AceBi in context.AceBiMonthlyDaos
join Inst in context.Instruments on AceBi.Naid equals Inst.AceId
join FinAcct in context.FinancialAccounts on AceBi.PositionAccountKey equals
FinAcct.AceClientId where AceBi.PositionAccountKey != null
join Invst in context.Investments
on new a = Inst.Id, b = FinAcct.Id equals new a = Invst.InstrumentId, b = Invst.FinAcctId
select new AceBi, Invst).Distinct();
Output from SQL image 1
Output list in Entity image 2
linq-to-sql linq-to-entities
edited Nov 11 at 8:12
Mohammad
13.1k93158
asked Nov 11 at 1:14
SPG
113
As you can see, the output in the list has duplicated report date value or it just picks the first reportdate value for all 3 records related to na_id/aceclientid pair
– SPG
Nov 11 at 1:16
1
Esp. the second list as image is extremely hard to read. Therefore it's always discouraged to use screenshots of textual output in questions. You better paste the text itself, formatted properly. That said, duplicated data always has to do with non-unique primary keys in the EF class model.
– Gert Arnold
Nov 11 at 14:09
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have the below query which brings the position value.
If I run the SQL query obtained from the debug window the records look okay.
But the list doesn't seem to duplicate the value for Report Date field in c#.
var query = (from AceBi in context.AceBiMonthlyDaos
join Inst in context.Instruments on AceBi.Naid equals Inst.AceId
join FinAcct in context.FinancialAccounts on AceBi.PositionAccountKey equals
FinAcct.AceClientId where AceBi.PositionAccountKey != null
join Invst in context.Investments
on new a = Inst.Id, b = FinAcct.Id equals new a = Invst.InstrumentId, b = Invst.FinAcctId
select new AceBi, Invst).Distinct();
Output from SQL image 1
Output list in Entity image 2
linq-to-sql linq-to-entities
edited Nov 11 at 8:12
Mohammad
13.1k93158
asked Nov 11 at 1:14
SPG
113
I have the below query which brings the position value.
If I run the SQL query obtained from the debug window the records look okay.
But the list doesn't seem to duplicate the value for Report Date field in c#.
var query = (from AceBi in context.AceBiMonthlyDaos
join Inst in context.Instruments on AceBi.Naid equals Inst.AceId
join FinAcct in context.FinancialAccounts on AceBi.PositionAccountKey equals
FinAcct.AceClientId where AceBi.PositionAccountKey != null
join Invst in context.Investments
on new a = Inst.Id, b = FinAcct.Id equals new a = Invst.InstrumentId, b = Invst.FinAcctId
select new AceBi, Invst).Distinct();
Output from SQL image 1
Output list in Entity image 2
linq-to-sql linq-to-entities
linq-to-sql linq-to-entities
edited Nov 11 at 8:12
Mohammad
13.1k93158
asked Nov 11 at 1:14
SPG
113
edited Nov 11 at 8:12
Mohammad
13.1k93158
asked Nov 11 at 1:14
SPG
113
edited Nov 11 at 8:12
Mohammad
13.1k93158
edited Nov 11 at 8:12
Mohammad
13.1k93158
edited Nov 11 at 8:12
Mohammad
13.1k93158
13.1k93158
asked Nov 11 at 1:14
SPG
113
asked Nov 11 at 1:14
SPG
113
asked Nov 11 at 1:14
SPG
113
113
As you can see, the output in the list has duplicated report date value or it just picks the first reportdate value for all 3 records related to na_id/aceclientid pair
– SPG
Nov 11 at 1:16
1
Esp. the second list as image is extremely hard to read. Therefore it's always discouraged to use screenshots of textual output in questions. You better paste the text itself, formatted properly. That said, duplicated data always has to do with non-unique primary keys in the EF class model.
– Gert Arnold
Nov 11 at 14:09
add a comment |
As you can see, the output in the list has duplicated report date value or it just picks the first reportdate value for all 3 records related to na_id/aceclientid pair
– SPG
Nov 11 at 1:16
1
Esp. the second list as image is extremely hard to read. Therefore it's always discouraged to use screenshots of textual output in questions. You better paste the text itself, formatted properly. That said, duplicated data always has to do with non-unique primary keys in the EF class model.
– Gert Arnold
Nov 11 at 14:09
As you can see, the output in the list has duplicated report date value or it just picks the first reportdate value for all 3 records related to na_id/aceclientid pair
– SPG
Nov 11 at 1:16
As you can see, the output in the list has duplicated report date value or it just picks the first reportdate value for all 3 records related to na_id/aceclientid pair
– SPG
Nov 11 at 1:16
1
1
Esp. the second list as image is extremely hard to read. Therefore it's always discouraged to use screenshots of textual output in questions. You better paste the text itself, formatted properly. That said, duplicated data always has to do with non-unique primary keys in the EF class model.
– Gert Arnold
Nov 11 at 14:09
Esp. the second list as image is extremely hard to read. Therefore it's always discouraged to use screenshots of textual output in questions. You better paste the text itself, formatted properly. That said, duplicated data always has to do with non-unique primary keys in the EF class model.
– Gert Arnold
Nov 11 at 14:09
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
try convert it to dictionary first
Dictionary<int, DateTime> existingItems =
(from ObjType ot in TableObj).ToDictionary(x => x.Key);
answered Nov 11 at 8:24
dev stack
61
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
try convert it to dictionary first
Dictionary<int, DateTime> existingItems =
(from ObjType ot in TableObj).ToDictionary(x => x.Key);
answered Nov 11 at 8:24
dev stack
61
add a comment |
up vote
0
down vote
try convert it to dictionary first
Dictionary<int, DateTime> existingItems =
(from ObjType ot in TableObj).ToDictionary(x => x.Key);
answered Nov 11 at 8:24
dev stack
61
add a comment |
up vote
0
down vote
up vote
0
down vote
try convert it to dictionary first
Dictionary<int, DateTime> existingItems =
(from ObjType ot in TableObj).ToDictionary(x => x.Key);
answered Nov 11 at 8:24
dev stack
61
try convert it to dictionary first
Dictionary<int, DateTime> existingItems =
(from ObjType ot in TableObj).ToDictionary(x => x.Key);
answered Nov 11 at 8:24
dev stack
61
answered Nov 11 at 8:24
dev stack
61
answered Nov 11 at 8:24
dev stack
61
answered Nov 11 at 8:24
dev stack
61
61
add a comment |
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53244992%2flinq-query-returns-proper-count-but-duplicates-data%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
As you can see, the output in the list has duplicated report date value or it just picks the first reportdate value for all 3 records related to na_id/aceclientid pair
– SPG
Nov 11 at 1:16
1
Esp. the second list as image is extremely hard to read. Therefore it's always discouraged to use screenshots of textual output in questions. You better paste the text itself, formatted properly. That said, duplicated data always has to do with non-unique primary keys in the EF class model.
– Gert Arnold
Nov 11 at 14:09