linq to entities - Not getting data of Include object -- Entity Framework -

i getting problem "include" in entity framework. lets assume have 2 tables foreign key relation.

   var result = (from u in entity.table1.include("table2")           join o in entity.table2 on u.column1 equals o.column1           u.column2 == “abc” && u.column3 == ‘xyz’            && o.column5 == organizationcode            select u).firstordefault(); 

with above query not returning table2 object data in result though have proper data in database.

the issue have found above query is, if query having "include" "join", ef not considering "include" tables. assumption.

after spending time got data writing dummy query below that. please see both queries below.

  var result = (from u in entity.table1.include("table2")           join o in entity.table2 on u.column1 equals o.column1           u.column2 == “abc” && u.column3 == ‘xyz’            && o.column5 == organizationcode            select u).firstordefault();     var resultorg = (from o in entity. table2                     o.column5 == organizationcode                      select o).firstordefault(); 

after executing both queries getting include (table2) data in result variable. in case unnecessarily executing 1 query want avoid.
please suggest me doing wrong.

you cannot use include if using join. there way around that. trying filtering include not possible.

you can this:

var result = (from u in entity.table1.include("table2")               u.column2 == “abc” && u.column3 == ‘xyz’ &&                      u.table2.any(o => o.column5 == organizationcode)                select u).firstordefault(); 

but include table2 entities filtered table1 entities. cannot restrict included values having organization code.

to filter navigation properties must use projection:

var result = (from u in entity.table1               u.column2 == “abc” && u.column3 == ‘xyz’ &&                      u.table2.any(o => o.column5 == organizationcode)                select new                    {                       table1 = u                       table2 = u.table2.where(o => o.column5 == organizationcode)                               }).firstordefault();  

you must project either anonymous type or custom type.

the reason why second query works automatic wiring of relations tracked properties , way how filter relations in such case enough:

var result = (from u in entity.table1               u.column2 == “abc” && u.column3 == ‘xyz’ &&                      u.table2.any(o => o.column5 == organizationcode)                select u).firstordefault();   var resultorg = (from o in entity. table2                  o.column5 == organizationcode                   select o).firstordefault();