Conversion from string to int in LINQ to SQL query

Ruchi Sharavat

5y7.2kLINQ

In Sql i have a string column which stores time value in mm:ss format. While writing LINQ to SQL query in C# I need to add that time value in date value which is another sql column.

To do that i will have to write

DBFunction.AddHours(DateColumn,(double)TimeColumn.Substring(0,2)

but it gives complie time error.

I tried

DBFunction.AddHours(DateColumn,Convert.ToDouble(TimeColumn.Substring(0,2)) but it gives run time error as it is non supported method.

What is the solution if anyone can help.

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
No matter whether it is a DBContext or IList (ultimately they all are collections of data in different format), when you write query in LINQ, in behind the scences they all have to follow the same path and convert it into raw SQL query.

Just follow the principal pattern and do your stuff

Note: If you are using any Anonymous Type then use var keyword to itereate the collection because this doesn't exists in your model
+2
Ruchi Sharavat5y ago
Yes but in example, we have it as IList on which we are executing the query but when i use it with DB context it breaks in SQL IQUERABLE.

var q= _unitOfWork.TableA.GetAll()
join TableB on some column
select ( new Mymodel {
prop1= DBFunction.AddHours(AdminssionDate, double(AdmissionTime.Substring(0,2));
}
+2
Amit Gupta5y ago
Corrected the fiddle, please check
https://dotnetfiddle.net/ZwafMO

It should be like AddHours (you had omitted the s)
var teenAgerStudent = from s in studentList
                  select new Student() {
                  StudentName = s.StudentName,
                  Age = s.Age.Substring(0, 2),
        FullAge=s.DOB.AddHours(Convert.ToDouble(s.Age.Substring(0, 2))).ToString()
Do let me know, if it resolves your issue
+2
Ruchi Sharavat5y ago
Still not useful. Can you please check this -https://dotnetfiddle.net/q1Yqhi

as i'll have to add time which is in string format to date
+2
Amit Gupta5y ago
Currently I am on limited resources, here is an fiddler example https://dotnetfiddle.net/ycPO0M
+2
Amit Gupta5y ago
Add a bracket () select ( new Mymodel() {prop... Rest code }
+2
Ruchi Sharavat5y ago
This is the single query in which i will have to add time in date field because both columns are in single table and that is getting used in where condition.

Eg:
TableA has 2 columns

AdminssionDate Date ='2020-02-02'
AdmissionTime varchar(10) ='05:10'

My query is
var q= _unitOfWork.TableA.GetAll()
join TableB on some column
select ( new Mymodel {
prop1= DBFunction.AddHours(AdminssionDate, double(AdmissionTime.Substring(0,2));
}

We don't want to use ToList() or AsEnumerable() as we fetch number of records in one query
+2
Rijwan Ansari5y ago
Hi,

Sample way to handle this is first get date value in a veriable then time into another and merge to gat required format.
DateTime dtFromDB = GetDateDBvalue ///You can get value here
var strDateTable1 = dtFromDB.ToString("MM/dd/yyyy");
var strTimefromDB = gettime // get time form DB

Var resultDatetime = strDateTable1 + " " + strTimefromDB;
+1
Amit Gupta5y ago
What is the datatype of your parameter? Int or double. Please share full code snippet and full exception details
+2