linq combined query

[Chuck P]

I tried to make some complex linq queries easier to read by pulling some of
the queries out.

This works fine:
var qAllJobDescriptions = from j in dc.Jobs
select j;

var qAllOracleJobCodes = from o in dc.OracleJobCodes
select o.JOBCODE;

GridView_Ex3.DataSource = from j in qAllJobDescriptions
where
!(qAllOracleJobCodes).Contains(j.OracleJobCode)
select j;


This works fine:
var qActiveJobDescriptions = from j in dc.Jobs
where DateTime.Today >= j.StartDate
&& (j.EndDate == null || (DateTime.Today <
j.EndDate.Value.AddDays(1)))
select j;

var qActiveOracleJobCodes ="";

GridView_Ex2.DataSource = (from j in qActiveJobDescriptions
where !(from o in dc.OracleJobCodes
where
DateTime.Today >= o.DATEFROM
&& (o.DATETO == null ||
(DateTime.Today < o.DATETO.Value.AddDays(1)))
select
o.JOBCODE).Contains(j.OracleJobCode)
select new { j.OracleJobCode })


This blows up when I move the where !(....).Contains into its own query.
Seems odd because I did a similar thing in the first example.
:
var qActiveJobDescriptions = from j in dc.Jobs
where DateTime.Today >= j.StartDate
&& (j.EndDate == null || (DateTime.Today <
j.EndDate.Value.AddDays(1)))
select j;

var qActiveOracleJobCodes =from o in dc.OracleJobCodes
where
DateTime.Today >= o.DATEFROM
&& (o.DATETO == null ||
(DateTime.Today < o.DATETO.Value.AddDays(1)))
select o.JOBCODE;

GridView_Ex2.DataSource = (from j in qActiveJobDescriptions
where
!(qActiveOracleJobCodes).Contains(j.OracleJobCode)
select new { j.OracleJobCode })



((System.Exception)((new
System.Linq.SystemCore_EnumerableDebugView<<string>>(((System.Data.Linq.DataQuery<<string>>)(GridView_Ex2.DataSource)))).Items)).StackTrace

at System.Data.Linq.SqlClient.SqlFactory.Member(SqlExpression expr,
MemberInfo member)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMemberAccess(MemberExpression
ma)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitCast(UnaryExpression c)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitBinary(BinaryExpression
b)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitBinary(BinaryExpression
b)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitWhere(Expression
sequence, LambdaExpression predicate)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitSelect(Expression
sequence, LambdaExpression selector)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitContains(Expression
sequence, Expression value)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitUnary(UnaryExpression u)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitWhere(Expression
sequence, LambdaExpression predicate)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitSelect(Expression
sequence, LambdaExpression selector)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node)
at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query,
SqlNodeAnnotations annotations)
at
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at
System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,

Did you get any error message? It seems you only pasted the StackTrace in
thread. I cannot found error message in it.

Moreover, I noticed you wrote lots of Linq Combined Query. But, I'm not
sure which query thrown the exception.

var qActiveJobDescriptions = from j in dc.Jobs....?
or
var qActiveOracleJobCodes =from o in dc.OracleJobCodes.....?
or
GridView_Ex2.DataSource = (from j in qActiveJobDescriptions....?

I suggest you may convert the query into list. Thereby, we can check which
query results the error.
For example:
var qActiveJobDescriptions = from....
*qActiveJobDescriptions.ToList();
var qActiveOracleJobCodes =from o...
*qActiveOracleJobCodes.ToList();
GridView_Ex2.DataSource = (from j in qActiveJobDescriptions

Hope this helps. Please feel free to let us know if you have any more
concern. We are glad to assist you.
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Chuck P]

Actually it did not throw an exception.
However, when I went to assign the query to the datagrid's datasource I got
a null reference exception. The error shown came during debug when I went to
expand the query.
When I do the queries singularly they work fine.

When I use qActiveOracleJobCodes instead of typing "from o in
dc.OracleJobCodes
where
DateTime.Today >= o.DATEFROM
&& (o.DATETO == null ||
(DateTime.Today < o.DATETO.Value.AddDays(1)))
select o.JOBCODE; "

in the final assignment, I get the problem.

Hello Chuck,

Did you get any error message? It seems you only pasted the StackTrace in
thread. I cannot found error message in it.

Moreover, I noticed you wrote lots of Linq Combined Query. But, I'm not
sure which query thrown the exception.

var qActiveJobDescriptions = from j in dc.Jobs....?
or
var qActiveOracleJobCodes =from o in dc.OracleJobCodes.....?
or
GridView_Ex2.DataSource = (from j in qActiveJobDescriptions....?

I suggest you may convert the query into list. Thereby, we can check which
query results the error.
For example:
var qActiveJobDescriptions = from....
*qActiveJobDescriptions.ToList();
var qActiveOracleJobCodes =from o...
*qActiveOracleJobCodes.ToList();
GridView_Ex2.DataSource = (from j in qActiveJobDescriptions

Hope this helps. Please feel free to let us know if you have any more
concern. We are glad to assist you.
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,
Thanks for your reply.

It's really difficult to perform further research without exact error
message. Your linq query looks fine. I cannot image why it failed after you
replaced "from o in dc.OracleJobCodes....." with qActiveOracleJobCodes. It
seems I need to reproduce the issue on my side. Could you please send me
the reproduce steps?
I'm willing to dig into the issue if you can send me a simple reproduce
project and Database. My email address is (e-mail address removed).

Please feel free to let me know if you have any more concern. We are glad
to assist you.
Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Chuck P]

Probably not worth the time.
My guess since it is a direct replacement that the LINQ interpreter has some
issues with embedding complex queries.

var qActiveJobDescriptions = from j in dc.Jobs
where DateTime.Today >= j.StartDate
&& (j.EndDate == null || (DateTime.Today <
j.EndDate.Value.AddDays(1)))
select j;

// var qActiveOracleJobCodes =from o in dc.OracleJobCodes
//where
// DateTime.Today >= o.DATEFROM
// && (o.DATETO == null ||
(DateTime.Today < o.DATETO.Value.AddDays(1)))
//select
o.JOBCODE).Contains(j.OracleJobCode;

GridView_Ex2.DataSource = (from j in qActiveJobDescriptions
where !(from o in dc.OracleJobCodes
where
DateTime.Today >= o.DATEFROM
&& (o.DATETO == null ||
(DateTime.Today < o.DATETO.Value.AddDays(1)))
select
o.JOBCODE).Contains(j.OracleJobCode)
select new { j.OracleJobCode })
.Join(dc.OracleJobCodes,aj =>
aj.OracleJobCode, or => or.JOBCODE,(aj, or) => new
{aj.OracleJobCode,or.JOBFAMILY,or.JOBTITLE}) ;

GridView_Ex2.DataBind();

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,
Thanks for your reply.

In order to reproduce the issue, we need to create a database with the same
schema as yours, import real data into database, and test the query on
that. Could you please paste your database schema if it's possible for you?
Now, I'm afraid to say we cannot reproduce the issue without real data. I'm
willing to dig into the problem if we can get the database schema.
Actually, this is very import on repro. Anyway, if you are busy on other
tasks so far, and haven't time to investigate the issue now, please also
feel free to let us know. I will log the problem into our DB. You can
re-call us when you are available.

Hope this helps. Please feel free to let us know if you have any more
concern. We are glad to assist you.
Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.