HasValue and != null incorrect translation to sql

[toddtsic]

I have a linq query that fails in sql because .HasValue or != null gets translated into IS NOT NULL rather than case statements.

This may reflect a misunderstanding on my part, but this seems to be the case.

linq query (note the fee_base, fee_discount, fee_latefee, those values can come from a team, and if not there the age group the team belongs to, and if not there the league the age group belongs to. This is a coalesce, or isnull scenario except that 0 is allowed so have to test, can't coalesce)

List<Job_AvailableTeamRegistration_Detail> listJobAvailableTeams_Detail = await (
            from t in db.teams
            join j in db.Jobs on t.jobID equals j.jobID
            join ag in db.agegroups on t.agegroupID equals ag.agegroupID
            join jl in db.Job_Leagues on ag.leagueID equals jl.leagueID
            where
                t.jobID == cd.JobParams.id
                && jl.jobID == cd.JobParams.id
                && t.active == true
                && t.bAllowSelfRostering == true
            select new Job_AvailableTeamRegistration_Detail()
            {
                dob_min = t.dobMin ?? ag.dobMin ?? null,
                dob_max = t.dobMax ?? ag.dobMax ?? null,
                fee_deposit = t.perRegistrantDeposit ?? 0,
                fee_base = (t.perRegistrantFee.HasValue && t.perRegistrantFee > 0) ? t.perRegistrantFee : (ag.rosterFee.HasValue && ag.rosterFee > 0) ? ag.rosterFee : 0,
                fee_discount = (t.discountFee.HasValue && t.discountFee > 0 && DateTime.Now > t.discountFeeStart && DateTime.Now < t.discountFeeEnd) ? t.discountFee : (ag.discountFee.HasValue && ag.discountFee > 0 && DateTime.Now > ag.discountFeeStart && DateTime.Now < ag.discountFeeEnd) ? ag.discountFee : (jl.discountFee.HasValue && jl.discountFee > 0 && DateTime.Now > jl.discountFeeStart && DateTime.Now < jl.discountFeeEnd) ? jl.discountFee : 0,
                fee_latefee = (t.lateFee != null && t.lateFee > 0 && DateTime.Now > t.lateFeeStart && DateTime.Now < t.lateFeeEnd) ? t.lateFee : (ag.lateFee != null && ag.lateFee > 0 && DateTime.Now > ag.lateFeeStart && DateTime.Now < ag.lateFeeEnd) ? ag.lateFee : (jl.lateFee != null && jl.lateFee > 0 && DateTime.Now > jl.lateFeeStart && DateTime.Now < jl.lateFeeEnd) ? jl.lateFee : 0,
                gender = t.gender,
                bAddProcessingFees = j.bAddProcessingFees,
                fee_processing = 0,
                grad_year_max = t.grad_year_max ?? ag.grad_year_max ?? null,
                grad_year_min = t.grad_year_min ?? ag.grad_year_min ?? null,
                roster_count = (from c in listTeamIDCounts where c.teamID == t.teamID select c.playerCount).SingleOrDefault(),
                roster_max = t.maxCount,
                school_grade_max = t.school_grade_max ?? ag.school_grade_max ?? null,
                school_grade_min = t.school_grade_min ?? ag.school_grade_min ?? null,
                teamID = t.teamID,
                teamName = t.teamName
            }
        ).ToListAsync();

SQL SERVER 2014 sql translation:

exec sp_executesql N'SELECT [t].[dobMin], [ag].[dobMin], NULL, [t].[dobMax], [ag].[dobMax], NULL, [t].[perRegistrantDeposit], [t].[perRegistrantFee] IS NOT NULL, [t].[perRegistrantFee], [ag].[rosterFee] IS NOT NULL, [ag].[rosterFee], [t].[discountFee] IS NOT NULL, [t].[discountFee], @__Now_6 > [t].[discountFeeStart], @__Now_7 < [t].[discountFeeEnd], [ag].[discountFee] IS NOT NULL, [ag].[discountFee], @__Now_9 > [ag].[discountFeeStart], @__Now_10 < [ag].[discountFeeEnd], [jl].[discountFee] IS NOT NULL, [jl].[discountFee], @__Now_12 > [jl].[discountFeeStart], @__Now_13 < [jl].[discountFeeEnd], [t].[lateFee] IS NOT NULL, [t].[lateFee], @__Now_16 > [t].[lateFeeStart], @__Now_17 < [t].[lateFeeEnd], [ag].[lateFee] IS NOT NULL, [ag].[lateFee], @__Now_19 > [ag].[lateFeeStart], @__Now_20 < [ag].[lateFeeEnd], [jl].[lateFee] IS NOT NULL, [jl].[lateFee], @__Now_22 > [jl].[lateFeeStart], @__Now_23 < [jl].[lateFeeEnd], [t].[gender], [j].[bAddProcessingFees], [t].[grad_year_max], [ag].[grad_year_max], NULL, [t].[grad_year_min], [ag].[grad_year_min], NULL, [t].[teamID], [t].[maxCount], [t].[school_grade_max], [ag].[school_grade_max], NULL, [t].[school_grade_min], [ag].[school_grade_min], NULL, [t].[teamName]
FROM [Leagues].[teams] AS [t]
INNER JOIN [Jobs].[Jobs] AS [j] ON [t].[jobID] = [j].[jobID]
INNER JOIN [Leagues].[agegroups] AS [ag] ON [t].[agegroupID] = [ag].[agegroupID]
INNER JOIN [Jobs].[Job_Leagues] AS [jl] ON [ag].[leagueID] = [jl].[leagueID]
WHERE ([t].[jobID] = @__cd_JobParams_id_0) AND ([jl].[jobID] = @__cd_JobParams_id_1) AND ([t].[active] = 1) AND ([t].[bAllowSelfRostering] = 1)',N'@__Now_6 datetime,@__Now_7 datetime,@__Now_9 datetime,@__Now_10 datetime,@__Now_12 datetime,@__Now_13 datetime,@__Now_16 datetime,@__Now_17 datetime,@__Now_19 datetime,@__Now_20 datetime,@__Now_22 datetime,@__Now_23 datetime,@__cd_JobParams_id_0 uniqueidentifier,@__cd_JobParams_id_1 uniqueidentifier',@__Now_6='2015-06-23 09:08:52.960',@__Now_7='2015-06-23 09:08:52.960',@__Now_9='2015-06-23 09:08:52.960',@__Now_10='2015-06-23 09:08:52.960',@__Now_12='2015-06-23 09:08:52.960',@__Now_13='2015-06-23 09:08:52.960',@__Now_16='2015-06-23 09:08:52.963',@__Now_17='2015-06-23 09:08:52.963',@__Now_19='2015-06-23 09:08:52.963',@__Now_20='2015-06-23 09:08:52.963',@__Now_22='2015-06-23 09:08:52.963',@__Now_23='2015-06-23 09:08:52.963',@__cd_JobParams_id_0='2ACEA123-D57A-E311-BF81-F04DA202060B',@__cd_JobParams_id_1='2ACEA123-D57A-E311-BF81-F04DA202060B'

[maumar]

@toddtsic simple workaround could be to execute the projection on the client. It won't incur (much of) a perf hit since translation of joins and filter seem to be fine and therefore they can run on the server.

However we should be doing a correct translation either way, so its definitely a bug.

[toddtsic]

Thanks muamar, that's exactly what I did and yes it works just fine. Do you know if there are any estimates of specific ef components being available esp stored procedures. I have an important app that I want to deliver the second mvc6 go public, and this is probably the most important feature I'm missing right now. Lack of lazy loading turns out not to be much of a problem, it makes me code more efficiently, picking my joins and exact target fields. This issue here is also easily addressed...

[maumar]

perhaps @rowanmiller and/or @divega can shed more light on the timelines & schedule

[rowanmiller]

We don't have a timeline to share for SPROC support. You can query from them using the new FromSql capabilities, but saving isn't supported yet. It's close to the top of our backlog but it won't be in the initial RTM for ASP.NET 5.

[toddtsic]

Thanks for the update Rowan. Is there an ETA on RTM and will the RTM advise
against production? I'll probably be ready with a new app shortly but
won't release to clients until MS blesses the release with "can use in
production systems"...

Todd

On Mon, Jun 29, 2015 at 10:51 AM, Rowan Miller [email protected]
wrote:

We don't have a timeline to share for SPROC support. You can query from
them using the new FromSql capabilities, but saving isn't supported yet.
It's close to the top of our backlog but it won't be in the initial RTM for
ASP.NET 5.

—
Reply to this email directly or view it on GitHub
#2450 (comment)
.

[rowanmiller]

@toddtsic I can't give you the dates as it is up to the ASP.NET team to announce them (I know they are working on publically sharing the schedule). You will be fine to use it in production. We won't be recommending EF7 as a general replacement for EF6 until we have sproc support etc. but production use will be fully supported as long as you are ok with the limitations.

[toddtsic]

Hmm, I'm wondering whether I should insert EF 6x. Do you agree with the
recipe below from so?

You need to manually set the connection string when creating the context
since it can't get it from the web.config

so you can do this

public class MyContext : DbContext {
public MyContext(string connectionString) : base(connectionString) {
}}
var context = new MyContext("myConnectionString");

if you want to get the connection string from the config.json, then try this

IConfiguration configuration = new
Configuration().AddJsonFile("config.json");var connectionString =
configuration["Data:DefaultConnection:ConnectionString"]);

and if you want to inject the context into the DI Container, then I added a
factory like this

public static class MyContextFactory{
public static MyContext GetContext() {
IConfiguration configuration = new
Configuration().AddJsonFile("config.json");
return new
MyContext(configuration["Data:DefaultConnection:ConnectionString"]);
}
}

and then added this in startup.cs

services.AddTransient((a) => MyContextFactory.GetContext());

share http://stackoverflow.com/a/29572219/5019943edit
http://stackoverflow.com/posts/29572219/edit
answered Apr 10 at 23:32
http://stackoverflow.com/users/187283/tom
Tom http://stackoverflow.com/users/187283/tom
332110

On Mon, Jun 29, 2015 at 12:05 PM, Rowan Miller [email protected]
wrote:

@toddtsic https://github.com/toddtsic I can't give you the dates as it
is up to the ASP.NET team to announce them (I know they are working on
publically sharing the schedule). You will be fine to use it in production.
We won't be recommending EF7 as a general replacement for EF6 until we have
sproc support etc. but production use will be fully supported as long as
you are ok with the limitations.

—
Reply to this email directly or view it on GitHub
#2450 (comment)
.

[smitpatel]

similar to #3703