CODEWITHSUNDEEP
c#linq
Arantuath
Arantuath

Reputation: 243

Concatenation in a linq statement

string test = "/";
var results = from table1 in data2.AsEnumerable()
              join table2 in data1.AsEnumerable()
              on ((string)(table1["ANo"]) + test + (string)table1["MNo"]) equals (string)table2["File_Code"]
              where (float)table1["c1"] != 0
              && (string)table1["Case_Status"] == "Open"
              select new
              {
                  ACode = (int)table1["ACode"],
                  ANo = (int)table1["ANo"],
                  c1 = (int)table1["c1"]
              };

Getting a error:

Specified Cast is invalid on ((string)(table1["ANo"]) + test + (string)table1["MNo"]) equals (string)table2["File_Code"].

So in my linq I am trying to match ANo/MNo in one database to File_Code in another where ANo & MNo are different columns in the first database, any ideas?

Upvotes: 2

Views: 186

Answers (3)

Arantuath
Arantuath

Reputation: 243

var results1 = from table1 in data2.AsEnumerable()
                       select new
                       {
                           A_M = table1["ANo"] + "\" + table1["MatterNo"],
                           ANo = table1["ANo"],
                           c1 = table1["c1"].Equals(DBNull.Value) ? 0 : Convert.ToInt32(table1["c1"]),
                           //Case_Status = Convert.ToString(table1["Case_ Status"])
                       };
        var results = from table1 in results1
                      join table2 in data1.AsEnumerable()
                      on table1.A_M equals (string)table2["File_Code"]
                      where table1.c1 != 0
                      && (string)table2["Case_Status"] == "Open"
                      orderby table1.ANo
                      select new
                      {
                          cCode = table2["acAccountCode"],
                          ANo = table1.ANo,
                          c1 = table1.c1
                      };

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109109

When you've got code like

object a = "1";
var a2 = (string)a;

You're only changing the compile-time type of a from object to string, which is called casting. This only works because a already is a string (the actual type). If you do

object a = 1;
var a2 = (string)a;

You run into a runtime exception (InvalidCastException) because an integer can't act as (cast to) a string. That's what's going on in your code. At least one of the objects you try to cast to string is not actually a string.

The remedy is simple: use ToString(). This converts the object to a string. Conversion converts the actual type of an object.

Upvotes: 2

Zoran Horvat
Zoran Horvat

Reputation: 11301

(string)(table1["ANo"]) is actually a call to a conversion function in .NET, and there is no corresponding function in the underlying storage engine. Therefore, when LINQ provider is about to actualize the query in form of a particular storage-specific expression tree, the operation fails because LINQ provider cannot find an appropriate function which to use.

If item such as ANo is not already a string (e.g. varchar or something similar), then you probably need to call specific provider's method such as SqlFunctions.StringConvert(table1["ANo").

Typical examples where .NET code cannot be converted by the LINQ provider are date/time functions (e.g. DateTime.AddSeconds, etc.).

Upvotes: 2

Related Questions