Error from use of C# Linq SQL CONCAT

Question

I have the following three tables, and need to bring in information from two dissimilar tables.

Table baTable has fields OrderNumber and Position.
Table accessTable has fields OrderNumber and ProcessSequence (among others)
Table historyTable has fields OrderNumber and Time (among others).

.

var progress = from ba in baTable                                   
           from ac in accessTable
           where ac.OrderNumber == ba.OrderNumber
           select new {
                Position = ba.Position.ToString(),
                Time = "", 
                Seq = ac.ProcessSequence.ToString()
           };
progress = progress.Concat(from ba in baTable
                       from hs in historyTable
                       where hs.OrderNumber == ba.OrderNumber
                       select new {
                           Position = ba.Position.ToString(),
                           Time = String.Format("{0:hh:mm:ss}", hs.Time),
                           Seq = ""
                       });
int searchRecs = progress.Count();

The query compiles successfully, but when the SQL executes during the call to Count(), I get an error

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Clearly the two lists each have three items, one of which is a constant. Other help boards suggested that the Visual Studio 2010 C# compiler was optimizing out the constants, and I have experimented with alternatives to the constants.

The most surprising thing is that, if the Time= entry within the select new {...} is commented out in both of the sub-queries, no error occurs when the SQL executes.

Try changing Time = String.Format("{0:hh:mm:ss}", hs.Time) to Time = "foo" — Adam Robinson
– Adam Robinson, Commented Oct 27, 2011 at 17:51

Wouter de Kort · Accepted Answer · 2011-10-27 17:52:41Z

1

I actually think the problem is that Sql won't recognize your String.Format(..) method.

Change your second query to:

progress = progress.Concat(from ba in baTable
                       from hs in historyTable
                       where hs.OrderNumber == ba.OrderNumber
                       select new {
                           Position = ba.Position.ToString(),
                           Time = hs.Time.ToString(),
                           Seq = ""
                       });

After that you could always loop trough the progress and format the Time to your needs.

answered Oct 27, 2011 at 17:52

Wouter de Kort♦

40k13 gold badges88 silver badges104 bronze badges

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

Collectives™ on Stack Overflow

Error from use of C# Linq SQL CONCAT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related