Creating LINQ from SQL

Question

I am trying to create Linq query from following Sql but not able to get it work.

SQL

"select distinct(roomName) as RoomName, tblroomid as RoomId
 from TblMaster,tblrooms 
 where tblrooms.tblroomid = TblPresentationMaster.tblroomid 
 and convert(datetime, PDay, 101)='" + Pday + "'";

LINQ

(from tblRoom in tblRooms.AsEnumerable()
 join tblPMaster in tblMaster.AsEnumerable()
 on tblRoom.Field<int>("tblroomid") equals tblPMaster.Field<int>("tblroomid")
 where tblPMaster.Field<string>("pday") == Pday 
 select tblRoom.Field<string>("roomName")).Distinct();

If I try to run it

foreach (var myReader in query)
{
}  

I am getting following error

Specified cast is not valid.

These are values in following variable, hope this help in catching error

tblPMaster.pday = Jun 28 2011 12:00AM
Parameter Pday = 28/11/2011

I don't know where I am doing wrong. Can someone please help in getting correct LINQ query?

Answer 1

@javadotnetcoder, thank you for clarification. I think i found a solution...

Try it:

DataTable tblMaster = new DataTable();
DataColumn dc = new DataColumn("pday", Type.GetType("System.String"));
tblMaster.Columns.Add(dc);
tblMaster.Rows.Add(new Object[]{"Nov 28 2011 12:00AM"});
tblMaster.Rows.Add(new Object[]{"Apr 27 2013 11:10PM"});
tblMaster.Rows.Add(new Object[]{"Jul 18 2011 12:00AM"});
tblMaster.Rows.Add(new Object[]{"Mar 19 2012 10:01PM"});

DateTime PDay = new DateTime(2011,11,28);

//foreach(var row in tblMaster.AsEnumerable())
//{
//  Console.WriteLine("{0}", Convert.ToDateTime(row[0]));
//}

var qry = tblMaster.AsEnumerable()
         .Where(p=>Convert.ToDateTime(p.Field<string>("pday"))==PDay);
//qry.Dump();

Above code has been tested on LinqPad. Works as well ;)

Cheers, Maciej