Concatenation in a linq statement

Question

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?

Are ANo, MNo, and File_Code all string fields? And could any be null? — Jonny
– Jonny, Commented Dec 19, 2013 at 16:44
What is in data1 and data2? That's probably going to be quite important to answer the question :) Note that you have to use .ToString() instead of (string) if the data isn't actually a string, but rather a number for example. (string) does a cast, if possible, while .ToString() does a conversion, two very different things. — Luaan
– Luaan, Commented Dec 19, 2013 at 16:55
data2 is a table in a access database while data1 is from a mssql database. — Arantuath
– Arantuath, Commented Dec 19, 2013 at 17:08
@Luaan I only see your comment now (after answering myself). If you convert :) it into an answer I think that should get accepted. — Gert Arnold
– Gert Arnold, Commented Dec 19, 2013 at 20:34
@GertArnold I like your answer, it's simple and straight to the point. No reason to add another answer of mine ;) — Luaan
– Luaan, Commented Dec 20, 2013 at 8:42

Zoran Horvat · Accepted Answer · 2013-12-19 16:54:16Z

2

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

answered Dec 19, 2013 at 16:54

Zoran Horvat

11.4k3 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gert Arnold Over a year ago

The two .AsEnumerable() calls turn this in a pure linq-to-objects case.

Zoran Horvat Over a year ago

Be cautious with that - converting data tables to enumerable means bringing the cross join to memory before cutting them by the join condition. Results should be converted to objects (and subdued to LINQ to objects afterwards) only after the storage has done the best it can to construct the minimum of records that are really needed.

Gert Arnold · Accepted Answer · 2013-12-19 20:16:22Z

2

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.

answered Dec 19, 2013 at 20:16

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Comments

Arantuath · Accepted Answer · 2013-12-20 16:40:02Z

0

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
                      };

answered Dec 20, 2013 at 16:40

Arantuath

2432 gold badges5 silver badges15 bronze badges

1 Comment

Arantuath Over a year ago

Now my next challenge to somehow convert this IEnum to a datatable Data row Array.

Collectives™ on Stack Overflow

Concatenation in a linq statement

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related