Conversion from string to int in LINQ to SQL query

Question

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(02) but it gives complie time error. I tried DBFunction.AddHours(DateColumnConvert.ToDouble(TimeColumn.Substring(02)) but it gives run time error as it is non supported method. What is the solution if anyone can help.

Amit Gupta · Answer

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

Ruchi Sharavat · Answer

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(02)); }

Amit Gupta · Answer

Corrected the fiddle please check https://dotnetfiddle.net/ZwafMO It should be like AddHour s (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

Ruchi Sharavat · Answer

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

Amit Gupta · Answer

Currently I am on limited resources here is an fiddler example https://dotnetfiddle.net/ycPO0M

Amit Gupta · Answer

Add a bracket () select ( new Mymodel() {prop... Rest code }

Ruchi Sharavat · Answer

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(02)); } We don't want to use ToList() or AsEnumerable() as we fetch number of records in one query

Rijwan Ansari · Answer

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;

Amit Gupta · Answer

What is the datatype of your parameter? Int or double. Please share full code snippet and full exception details