CODEWITHSUNDEEP
c#sqllinq
afom
afom

Reputation: 61

Code throws Invalid cast exception: linq-sql join two tables

I'm new to sql-linq and I'm trying to join two tables using their common id which is MOTHERID. But the code below is throwing an "Invalid cast exception" at:

var firstQuery = (from s in _maternalvisitvaluedb.Value select s).ToList();

and the whole code looks like:

var firstQuery = (from s in _maternalvisitvaluedb.Value select s).ToList();
var secondQuery = (from t in _maternalcarevaluedb.Value select t).ToList();

var result = (from s in firstQuery
join k in secondQuery
on s.MotherId equals k.MotherId
where (DateTime)s.SecondVisit.Date == DateTime.Now.Date 
select s).ToList();

Thanks for your help!

Upvotes: 0

Views: 667

Answers (5)

CindyH
CindyH

Reputation: 3026

I know the original question has been answered, but in case anyone else bumps into this... I spent forever on this and solved it by changing 

on (Int64)table1["myField"] equals (Int64)table2["myField"]

to

on Convert.ToInt64(table1["myField"]) equals Convert.ToInt64(table2["myField"])

Upvotes: 0

D Stanley
D Stanley

Reputation: 152626

You can try using Equals() instead (which will accept nullable DateTime values)

var result = (from s in firstQuery
join k in secondQuery
on s.MotherId equals k.MotherId
where DateTime.Now.Date.Equals(s.SecondVisit.Date)
select s).ToList();

Also note that you're loading both tables into memory. You may be able to do the query in SQL if you don't do the intermediate queries:

var result = (from s in _maternalvisitvaluedb.Value
              join k in _maternalcarevaluedb.Value
                  on s.MotherId equals k.MotherId
              where DateTime.Now.Date.Equals(s.SecondVisit.Date)
              select s)
              .ToList();

A compromise would be to do the join in SQL and the filter in Linq-to-objects

var result = (from s in _maternalvisitvaluedb.Value
              join k in _maternalcarevaluedb.Value
                  on s.MotherId equals k.MotherId
              select new {s, k})
             .Where(sk => DateTime.Now.Date.Equals(s.SecondVisit.Date)
             .Select(sk => sk.s).ToList();

Upvotes: 0

Satyajit
Satyajit

Reputation: 2210

the code below is throwing an "Invalid cast exception" at the first line.

Looks like you have not selected the right table from your database '_maternalvisitvaluedb' which is giving the error.

'_maternalvisitvaluedb.Value' looks like a property of database and is not a table in the database. Verify if it's a table.

Upvotes: 0

user3752226
user3752226

Reputation:

s is not of type DateTime, remove your cast from the query and that should fix it.

Change

where (DateTime)s.SecondVisit.Date == DateTime.Now.Date

to 

where s.SecondVisit.Date == DateTime.Now.Date

Upvotes: 1

V2Solutions - MS Team
V2Solutions - MS Team

Reputation: 1127

you can use 

var result = (from s in firstQuery
join k in secondQuery
on s.MotherId equals k.MotherId
where DateTime.Compare(s.SecondVisit.Date, DateTime.Now.Date)<=0 
select s).ToList();

Upvotes: 0

Related Questions