c# 4.0 - wrong result possibly because MergeAs(AppendOnly) LINQ -

note

this might dup of this. don't clear answer that.

the problem

i got wrong result linq.

example

id     groupid   status     name ------------------------------------ guid1  guida     reserved   truck1 guid2  guida     reserved   truck2 guid3  guida     reserved   truck3 guid4  guida     reserved   truck4  *assume guids guids..  **id primary key (unique) 

case

if put where groupid == guida result correct (4 rows)

but, if put where groupid == guida && status == reserved result wrong (3 rows)

explanation

the query built expression tree. debug watch, query (that return wrong result) this

query.expression: ----------------- convert(value(system.data.objects.objectset`1[myeftable])) .mergeas(appendonly) .where(m => ( m.groupid == value(myfilterclass).groupid)) .where(m => m.status.contains(value(myfilterclass).statusstr)) .select(m => new myviewclass()  {     id = m.id, groupid = m.groupid, status = m.status, name = m.name }) .orderby(o => o.name).skip(0).take(10) 

then tried run similar query , returns correct result

assume e objectset<myeftable> , strguid guida  guid grid = new guid(strguid); e.asqueryable() .where(m => m.status.contains("reserved")) .where(m => m.groupid == grid) .select(m => new myviewclass() {     id = m.id, groupid = m.groupid, status = m.status, name = m.name }).orderby(o => o.name).skip(0).take(10); 

question

i'm clueless error..

1. if ef tables correct , query correct, why return wrong result?

2. is because mergeas(appendonly) ? because thing different that.

3. if unique key, should myviewclass ensure every row unique , should not merged (if case) ?

   ---------------------------------------------------------------------

update 2013/08/29

the result incorrect because have typos in 1 of query..

so, after changing few lot of here , there, try , error, till lost trace of whatever changed, commented, erased, works.. eureka!! what?

what did not change everything, it's lot of changes doesn't end different starting.. there's that!

then eureka moment ends , leave me tracing way find out wrong, because don't believe in miracles..

so here is..

the tables similar this:

 partsgroups     -----------  id  name     -----------  1   nails    -----------   items  -----------------  id  name   status  -----------------  2  table  empty  5  table  indent  6  door   empty  3  sofa   empty   partsgrouppairs        ------------------     id  groupid partid     ------------------     1       1       4      2       1       7      3       1       8      4       1      15      -------------------     parts                        ------------------------     id  name  itemid  status     ------------------------      4  xnail      2  empty       7  snail      5  empty       8  unail      6  empty      15  znail      3  empty      ------------------------    

the relationships

 partsgroups   partsgrouppairs      parts                      items  -----------   ------------------   ------------------------   -----------------  id  name      id  groupid partid   id  name  itemid  status   id  name   status  -----------   ------------------   ------------------------   -----------------  1   nails     1       1       4     4  xnail      2  empty     2  table  empty  1   nails     2       1       7     7  snail      5  empty     5  table  indent  1   nails     3       1       8     8  unail      6  empty     6  door   empty  1   nails     4       1      15    15  znail      3  empty     3  sofa   empty  -----------   -------------------  ------------------------   -----------------        1 <---> many       many <---> 1           many <------> 1    partsgroup.pairs collection of partsgrouppairs   partsgrouppair.group partsgroup   partsgrouppair.part part  part.item item  item.parts collection of parts 

so when select partsgroup name == 'nails' works perfectly, returns 4 rows

but why when select partsgroup name == 'nails' , status == 'empty' returns 3 rows?? (see below)

 partsgroups   partsgrouppairs      parts                      items  -----------   ------------------   ------------------------   -----------------  id  name      id  groupid partid   id  name  itemid  status   id  name   status  -----------   ------------------   ------------------------   -----------------  1   nails     1       1       4     4  xnail      2  empty     2  table  empty  1   nails     3       1       8     8  unail      6  empty     6  door   empty  1   nails     4       1      15    15  znail      3  empty     3  sofa   empty  -----------   -------------------  ------------------------   ----------------- 

this row didnt selected..

 partsgroups   partsgrouppairs      parts                      items  -----------   ------------------   ------------------------   -----------------  id  name      id  groupid partid   id  name  itemid  status   id  name   status  -----------   ------------------   ------------------------   -----------------  1   nails     2       1       7     7  snail      5  empty     5  table  indent  -----------   -------------------  ------------------------   ----------------- 

the mistake made @ where part. query built @ runtime coz separate entities, filters, views , paging modules. passing iqueryable here , there.

in case filter, every time filter, i'll add where. in case went below:

using(var db = new databasecontext())  {     objectset<partsgrouppair> d =          db.createobjectset<partsgrouppair>("partsgrouppair");      int searchgroupid = 1; // int instead of guid example     int searchstatus = "empty";       // filter specific partsgroup         iqueryable<partsgrouppair> e = d.where(m => m.group.id == searchgroupid);      // if want filter status     e = e.where(m => m.part.item.status == searchstatus));  // wrong!!      // want filter part.status, not item.status     // instead, should      e = e.where(m => m.part.status == searchstatus));  // right!!       // view      iqueryable<partsgrouppairview> f =          e.select(m => new partsgrouppairview()         {             id = m.id, groupid = m.groupid,              status = m.part.status, name = m.part.name              // etc..         });      // paging     f = f.orderby(o => o.name).skip(0).take(10);   } 

the wrong filtering makes linq ignore other parts, , returns 3 rows instead of 4 because found 1 item instead of 2 parts.

so in case, it's silly typo quite complex data..

it's frustrating when nothing works while seems okay..