Linq to SQL dealing with nullable value

Question

I have a legacy SQL script that join prob_contact table with other tables by comparing number of days with date range low_amt and high_amt. This work perfectly fine with SQL.

JOIN prob_contact p ON (s.end_date - s.start_date) BETWEEN p.low_amt AND p.high_amt

I want to convert the SQL script to Linq, but I'm having an issue on nullable TimeSpan? Please let me know if there is a solution for this problem.

let d = s.End_date - s.Start_date

Original T-SQL Command (datasource Oracle 12c by Joan Casteel)

SELECT (c.last + ', ' + c.first) AS name, s.start_date, s.end_date, p.con_freq
  FROM dbo.criminals c INNER JOIN dbo.sentences s ON c.criminal_id = s.criminal_id
       INNER JOIN dbo.prob_officers o ON o.prob_id = s.prob_id
       JOIN prob_contact p ON (s.end_date - s.start_date) BETWEEN p.low_amt AND p.high_amt
  ORDER BY name, s.start_date;

Linqpad script

var result = from c in Criminals 
                join s in Sentences on c.Criminal_id equals s.Criminal_id
                let d = s.End_date - s.Start_date
                from p in Prob_contacts where d < p.Low_amt
                orderby c.Last
                select new  {
                    name = c.Last + ", " + c.First,
                    startDate = s.Start_date,
                    freq = p.Con_freq,
                    d
                };

Anu Viswan · Accepted Answer · 2019-11-27 20:53:08Z

1

I hope I understood your requirement correctly. The Error (in the screenshot included with OP before it was edited after publishing of answer) is being thrown because you are comparing a TimeSpan (d is TimeSpan in your query) with Int32. From the description you have given, you would like to compare the number of days with Low_amt

What you would need is TimeSpan.TotalDays

from p in Prob_contacts where d.Value.TotalDays < p.Low_amt //(.Value) Since it is nullable as suggested in the error

edited Nov 27, 2019 at 20:53

answered Nov 27, 2019 at 20:30

Anu Viswan

18.2k4 gold badges27 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tu Tong Over a year ago

Hello Anu, thanks for the quick answer. let d = s.End_date - s.Start_date d is type nullable Timespan?. that might be null, because C# is a type safe langauge, it prevent from access the TotalDays property.

Tu Tong Over a year ago

I just update the screenshot above that would explain the Linq query does not work

Anu Viswan Over a year ago

@TuTong Please check my answer again. It is "d.Value.TotalDays". You have given "d.TotalDays".

Tu Tong Over a year ago

You are right. The Value property does the strict. Thank you very much.

Collectives™ on Stack Overflow

Linq to SQL dealing with nullable value

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related