There is two table in one table column is FID.. FID is in table 'tblRe ' and type in db is string and in other table column is MID .. MID is in table 'tblVeh' and type in db is int both values are same but names are different . i try to adjust but this shows error
string data = "[";
var re = (from veh in DB.tblVeh
join regh in DB.tblRe on
new{MID=veh .MID} equals new {MID=tblRe .FID}
where !(veh .VName == "")
group veh by veh .VName into g
select new
{
Name = g.Key,
cnt = g.Select(t => t.Name).Count()
}).ToList();
data += re.ToList().Select(x => "['" + x.Name + "'," + x.cnt + "]")
.Aggregate((a, b) => a + "," + b);
data += "]";
i try this
new{MID=veh .MID} equals new {MID=tblRe .FID}
error
The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.
any solution
It will be hard to join when the keys have different types. linq2sql needs to be able to transform your query into a sql statement to be able to execute it. I think the best solution would be to fetch the rows of intrest from the db and then do the join. This way any code can be used since it doesn't need to be transformed into sql.
//Get the list of items from tblVeh
var listOfVehs =
(from veh in DB.tblVeh
where !(veh.VName == "")
select veh).ToList();
//Get all MID from the vehs and convert to string.
var vehMIDs = listOfVehs.Select(x => x.MID.ToString()).ToList();
//Get all items from tblRe that matches.
var listOfRes = (from re in DB.tblRe
where vehMIDs.Contains(re.FID)
select re).ToList();
//Do a in code join
var re = (
from veh in listOfVehs
join regh in listOfRes on veh.MID.ToString() equals regh.FID
group veh by veh.VName into g
select new
{
Name = g.Key,
cnt = g.Select(t => t.Name).Count()
}).ToList();