How do I use attributes from a joined table in the where extension method

Question

I have a method using the following LINQ statement:

 

var _revenueDetails = from ard in dc.ACCOUNT_REVENUE_DETAIL

 

join acct in dc.ACCOUNT_SUMMARY on ard.Account_Summary_Key_ID equals acct.Account_Summary_Key_ID

 

join prod in dc.CIS_PRODUCT_TYPES on acct.Product_Type_Key_ID equals prod.Product_Type_Key_ID

 

join team in dc.TEAM on acct.Team_Key_ID equals team.Team_key_id

 

join comm in dc.IVS_COMMISSION on ard.Source_Key_ID equals comm.COMMISSION_KEY_ID into ivscommissions

 

select new RevenueDetail(ard.Revenue_Detail_Key_ID, acct.Account_ID, prod.Product_Type, ard.Revenue_Date,

ivscommissions.FirstOrDefault().PROD_CODE, ard.Gross_Revenue, ard.Payable_Revenue,

ard.Payable_Revenue_Rep_Adjusted);

I then have the following code to add where clauses depending on data supplied to the method

 

if (teamKeyID.HasValue) {

_revenueDetails.Where(t => t.team_key_id == teamKeyID);

}

This does not compile because the compiler cannot resolve t.team_key_id.

If I modify the linq query as follows and do not try to use the where extension method the query works fine:

var _revenueDetails = from ard in dc.ACCOUNT_REVENUE_DETAIL

 

join acct in dc.ACCOUNT_SUMMARY on ard.Account_Summary_Key_ID equals acct.Account_Summary_Key_ID

 

join prod in dc.CIS_PRODUCT_TYPES on acct.Product_Type_Key_ID equals prod.Product_Type_Key_ID

 

join team in dc.TEAM on acct.Team_Key_ID equals team.Team_key_id

 

join comm in dc.IVS_COMMISSION on ard.Source_Key_ID equals comm.COMMISSION_KEY_ID into ivscommissions

 

where

 

(teamKeyID == null || team.Team_key_id == teamKeyID) 

select new RevenueDetail(ard.Revenue_Detail_Key_ID, acct.Account_ID, prod.Product_Type, ard.Revenue_Date,

ivscommissions.FirstOrDefault().PROD_CODE, ard.Gross_Revenue, ard.Payable_Revenue,

ard.Payable_Revenue_Rep_Adjusted);

It appears that you cannot use any attribute that is not in the select clause in the .where extension method. While the second linq query works just fine the where extension method makes my code more flexable and readable. Is there any way I can use attributes that are not in the select clause in the where extension method.

Answer 1

Hi PBallance,

 

Welcome to LINQ to SQL forum!

 

The WHERE clause is performed on the table TEAM, so the WHERE extension method can be first put on dc.TEAM.

============================================================================================

var _revenueDetails = from ard in dc.ACCOUNT_REVENUE_DETAIL
                                 join acct in dc.ACCOUNT_SUMMARY on ard.Account_Summary_Key_ID equals acct.Account_Summary_Key_ID
                                 join prod in dc.CIS_PRODUCT_TYPES on acct.Product_Type_Key_ID equals prod.Product_Type_Key_ID

                                 join team in dc.TEAM.Where(t => teamKeyID == null || t.Team_Key_id == teamKeyID) on acct.Team_Key_ID equals team.Team_key_id

                                 join comm in dc.IVS_COMMISSION on ard.Source_Key_ID equals comm.COMMISSION_KEY_ID into ivscommissions

                                 select new RevenueDetail(ard.Revenue_Detail_Key_ID, acct.Account_ID, prod.Product_Type, ard.Revenue_Date,

                                                        ivscommissions.FirstOrDefault().PROD_CODE, ard.Gross_Revenue, ard.Payable_Revenue,

                                                        ard.Payable_Revenue_Rep_Adjusted);

========================================================================

 

Please test these codes.  If you have any questions, please feel free to let me know.

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Lingzhi's solution is a good one but if you are wondering why what you are doing won't work consider the last bit of your query which is select new RevenueDetail. 

Once the compiler sees that the query is now IQueryable<RevenueDetail> and you no longer have access to anything but RevenueDetail objects and additionally because you used a constructor that's the end of the line for LINQ to SQL as it can't know what properties mapped to what query parts. (If you use the list initializer syntax it can).

So what you effectively need to do is split the query definition into two parts - the first that does the join and gets the bits it needs but still has references to other things you want access to (such as team_key_id).  Then you can build the additional where clauses and finally project it into your final type.  LINQ to SQL will still run this as a single integrated query :)

Knowing this we can now reference team_key_id in the projection perhaps into an anonymous type (which won't actually be instantiated) and because it doesn't use a construtor LINQ to SQL can follow the chain back, e.g. - write the select on your first query definition line as as:

select new { ard.Revenue_Detail_Key_ID, acct.Account_ID, prod.Product_Type, ard.Revenue_Date, ivscommissions.FirstOrDefault().PROD_CODE, ard.Gross_Revenue, ard.Payable_Revenue, ard.Payable_Revenue_Rep_Adjusted, t.team_key_id };

Then your if line can simply do;

if (teamKeyID.HasValue) { _revenueDetails.Where(t => t.team_key_id == teamKeyID); }

and finally we complete the query definition by projecting it into your concrete RevenueDetails:

var _revenueDetails = from t in _revenueDetails select new RevenueDetail(t.Revenue_Detail_Key_ID, t.Account_ID, t.Product_Type, t.Revenue_Date, t.PROD_CODE, t.Gross_Revenue, t.Payable_Revenue, t.Payable_Revenue_Rep_Adjusted);

[)amien

Answer 3

Hi PBalllance,

Great guys from Microsoft have responded to the post and I would like to join also.
Damien provided an excellent suggestion and I would like to add more to it. Taking the advantage of LINQ's deferred execution (i.e. queries are not materialized until they are needed), an scablable solution would be include ALL joined tables in the first select statement, perform filtering using where methods, and finally select the desired columns. To demonstrate, have a look at his code:

//your original query
var query1 =
from ard in dc.ACCOUNT_REVENUE_DETAIL
join acct in dc.ACCOUNT_SUMMARY on ard.Account_Summary_Key_ID equals acct.Account_Summary_Key_ID 
join prod in dc.CIS_PRODUCT_TYPES on acct.Product_Type_Key_ID equals prod.Product_Type_Key_ID
join team in dc.TEAM on acct.Team_Key_ID equals team.Team_key_id
join comm in dc.IVS_COMMISSION on ard.Source_Key_ID equals comm.COMMISSION_KEY_ID into ivscommissions
select new { ard, acct, prod, team, comm }; //select ALL the tables


//now you can apply where clause to "any" of the tables
var query2 = query1.Where( q=>q.team.team_key_id == teamKeyID );

var query3 = query2.Where( q=>q.prod.product_id == someProductID );

...

//finally select the columns you need

var result = 
from q in query3
select new RevenueDetail(q.ard.Revenue_Detail_Key_ID, q.acct.Account_ID, q.prod.Product_Type, q.ard.Revenue_Date,
ivscommissions.FirstOrDefault().PROD_CODE, q.ard.Gross_Revenue, q.ard.Payable_Revenue,
q.ard.Payable_Revenue_Rep_Adjusted);

Hope that makes sense.

Regards,

---

Syed Mehroz Alam
My Blog | My Articles

Answer 4

Thanks guys... The answer from Lingzhi works but sort of misses the point. I really wanted to separate building the LINQ statement from the adding of the where clauses so I can have my method take in many nullable attributes and make the where clause dynamic. The ansewer from Damien and Syed both solve my problem but Syed's answer gives me access to every column in all joined tables for possible inclusion in the where clause. Both answers however give the crux of the answer that getting the result must be broken into three steps, one to get the data, one to add the dynamic where clause and the final one to build my non anonymous type. And because of the defered execution the only SQL that gets generated and executed is the last one that only brings back only the columns needed to build the non anonymous type. Very cool;-)

Answer 5

Hi PBallance,

 

Cool!   I am very glad that the problem has been resolved.   MSDN forums are full of creative ideas, helpful suggestions, and more important, kind community members!  That’s why I love our community so much.  J

 

Thank you, Syed and Damien for your replies.  Really helpful!  

 

PBallance, if you have any other LINQ related problems, please feel free to post here.  We are very happy to discuss them with you. 

 

Have a nice weekend, guys!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.