Values are same but names are different using linq

Question

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

You cannot simply compare an `int` to a `string`. You need to convert one or the other into the other type. — René Vogt, Jun 14 '16 at 06:57
Normally I would say `veh.MID.ToString()`, but I'm not sure if this is possible in this expression. — René Vogt, Jun 14 '16 at 06:58
this shows error.. LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression. — SUPER_USER, Jun 14 '16 at 07:01
Possible duplicate of [The type of one of the expressions in the join clause is incorrect in Entity Framework](http://stackoverflow.com/questions/19184019/the-type-of-one-of-the-expressions-in-the-join-clause-is-incorrect-in-entity-fra) — Gert Arnold, Jun 14 '16 at 07:06
*Not works* doesn't mean anything to me. *How* did you try it? Note that is says the "names and types" must match. If you ask a question at Stack Overflow you should always tell what you tried yourself. — Gert Arnold, Jun 14 '16 at 07:14

fhogberg · Accepted Answer · 2016-06-15T09:50:50.963

1

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

edited Jun 15 '16 at 09:50

answered Jun 14 '16 at 07:58

fhogberg

415
3
11

this shows error;;An error occurred while executing the command definition. See the inner exception for details. – SUPER_USER Jun 14 '16 at 08:11
And what do the inner exception says? – fhogberg Jun 15 '16 at 08:23
this error occur on catch..An error occurred while executing the command definition. See the inner exception for details. where i see inner exception? – SUPER_USER Jun 15 '16 at 08:28
You should be able to click somthing as "Exception details" and then expand the inner exception property of the grid that is displayed. – fhogberg Jun 15 '16 at 08:29
On what line is the exception thrown? – fhogberg Jun 15 '16 at 09:49
Perhaps we need to ToList the vehMIDs. Modified the code above. – fhogberg Jun 15 '16 at 09:51

score 0 · Answer 2 · answered Jun 23 '16 at 02:52

When performing a join, you need to make sure that the key types match. Since you're joining on a single property, an anonymous object isn't necessary, but the types must match. It'd be more applicable to join on strings so convert in property to a string.

var query =
    from v in db.tblVeh
    join r in db.tblRe on Convert.ToString(v.MID) equals r.FID
    where v.VName != ""
    group 1 by v.VName into g
    select $"['{g.Key}',{g.Count()}]";
var data = $"[{String.Join(",", query.AsEnumerable())}]";

Values are same but names are different using linq

2 Answers2