Solved

Create a UNION ALL between two tables in LINQ

Posted on 2008-10-31
14
4,389 Views
Last Modified: 2013-11-11
How do I create a UNION ALL between two tables in LINQ. C#
0
Comment
Question by:u2envy1
  • 7
  • 6
14 Comments
 

Author Comment

by:u2envy1
Comment Utility
Why is this not working ?

 var rsPunch = (from a in PunchCtx.AccessPunches
                               //from t in PunchCtx.TerminalTypes
                               join t in PunchCtx.TerminalTypes on a.Acp_Terminal_SI equals t.TrmT_ID_SI
                               where a.Acp_DateAndTime_DT >= dtFrom && a.Acp_DateAndTime_DT <= dtTo
                               select new
                               {
                                   a.Acp_Terminal_SI,
                                   a.Acp_DateAndTime_DT,
                                   t.TrmT_Name_VC10,
                                   a.Acp_EmployeeNumber_VC16,
                                   a.Acp_EDITEDBY_VC20,
                                   a.Acp_REASON_VC20


                               })
                               .Union
                               (from t in PunchCtx.TimeAtendancePunches
                                join te in PunchCtx.TerminalTypes on t.Tap_Terminal_SI equals te.TrmT_ID_SI
                                where t.Tap_DateAndTime_DT >= dtFrom && t.Tap_DateAndTime_DT <= dtTo
                                select new
                                {
                                    t.Tap_Terminal_SI,
                                    t.Tap_DateAndTime_DT,
                                    te.TrmT_Name_VC10,
                                    t.Tap_EmployeeNumber_VC16,
                                    t.Tap_EditedBy_VC20,
                                    t.Tap_Reason_VC20
                                   
                                });
0
 

Author Comment

by:u2envy1
Comment Utility
Ooops !! Error....

Error      6      The type arguments for method 'System.Linq.Enumerable.Union<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Collections.Generic.IEnumerable<TSource>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.      C:\Documents and Settings\Krivahnd\Desktop\Dev Server - Working Dir\Sharp\Sharp\Forms\FrmAddEmployee2.cs      676      32      Sharp
0
 
LVL 4

Expert Comment

by:novynov
Comment Utility
I believe the problem is with the differences between the property names on the 2 anonymous types (e.g. a.Acp_EDITEDBY_VC20 vs t.Tap_EditedBy_VC20). In order for the type checking to validate, the types on either side of the union need to match. In the case of anonymous types, this means their property names need to match...exactly...including case.

So I believe the fix is fairly simple...Assuming you can't / don't want to change your DB schema to make the column names match...give explict names to each property of each anonymous type that match. See snippet below.

Sorry I didn't answer this sooner. I didn't see your question until just now. Let me know if this works. If it doesn't, let me know, and I'll do my best to figure out the problem.
var rsPunch = (from a in PunchCtx.AccessPunches

                               //from t in PunchCtx.TerminalTypes

                               join t in PunchCtx.TerminalTypes on a.Acp_Terminal_SI equals t.TrmT_ID_SI

                               where a.Acp_DateAndTime_DT >= dtFrom && a.Acp_DateAndTime_DT <= dtTo

                               select new

                               {

                                   Terminal = a.Acp_Terminal_SI,

                                   DateAndTime = a.Acp_DateAndTime_DT,

                                   Name = t.TrmT_Name_VC10,

                                   EmployeeNumber = a.Acp_EmployeeNumber_VC16,

                                   EditedBy = a.Acp_EDITEDBY_VC20,

                                   Reason = a.Acp_REASON_VC20
 
 

                               })

                               .Union

                               (from t in PunchCtx.TimeAtendancePunches

                                join te in PunchCtx.TerminalTypes on t.Tap_Terminal_SI equals te.TrmT_ID_SI

                                where t.Tap_DateAndTime_DT >= dtFrom && t.Tap_DateAndTime_DT <= dtTo

                                select new

                                {

                                    Terminal = t.Tap_Terminal_SI,

                                    DateAndTime = t.Tap_DateAndTime_DT,

                                    Name = te.TrmT_Name_VC10,

                                    EmployeeNumber = t.Tap_EmployeeNumber_VC16,

                                    EditedBy = t.Tap_EditedBy_VC20,

                                    Reason = t.Tap_Reason_VC20

                                    

                                });

Open in new window

0
 

Author Comment

by:u2envy1
Comment Utility
This still does not work......

     var rsPunch = (from a in PunchCtx.AccessPunches orderby a.Acp_DateAndTime_DT descending
                                    join t in PunchCtx.TerminalTypes on a.Acp_Terminal_SI equals t.TrmT_ID_SI
                                    where a.Acp_EmployeeNumber_VC16 == empNo
                                   select new
                                   {
                                       PunchDescription = "A",
                                       Terminal = a.Acp_Terminal_SI,
                                       Date = a.Acp_DateAndTime_DT,
                                       TName = t.TrmT_Name_VC10,
                                       EmployeeNo = a.Acp_EmployeeNumber_VC16,
                                       EditedBy = a.Acp_EDITEDBY_VC20,
                                       Reason = a.Acp_REASON_VC20                              
                                                                           
                                       
                                   }
                                   ).Union
                                   (from t in PunchCtx.TimeAtendancePunches orderby t.Tap_DateAndTime_DT descending
                                    join te in PunchCtx.PunchesTerminals on t.Tap_Terminal_SI equals te.Trm_ID_SI
                                    where t.Tap_EmployeeNumber_VC16 == empNo
                                    select new
                                    {
                                        PunchDescription = "T",
                                        Terminal = t.Tap_Terminal_SI,
                                        Date = t.Tap_DateAndTime_DT,
                                        TName = te.TRM_TerminalName_VC15,
                                        EmployeeNo = t.Tap_EmployeeNumber_VC16,
                                        EditedBy = t.Tap_EditedBy_VC20,
                                        Reason = t.Tap_Reason_VC20


                                    });
0
 
LVL 4

Expert Comment

by:novynov
Comment Utility
Hmmm...Can you please let me know exactly what error you are now seeing?  If it is still a type inference issue...I'm going to take a guess...since I don't have your exact schema at hand. Are the C# types of each of the properties exactly the same? If not, I believe they will need to be. The easiest way to resolve that is to define a new class and do a select new NewClass {...}.

Please let me know.
0
 

Author Comment

by:u2envy1
Comment Utility
I corrected all dataTypes & still the problem persists.
How do I combine all records from both tables into a view or something ?
This union works in sql server 2005 thou.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:novynov
Comment Utility
This is strange, as I did a similar query against a test db that I had, and had no problems using anonymous types.

So, did you have some property types that were different between the anonymous types? If so, which ones did you change? Can you include an updated code snippet, and/or some db schema info?
0
 

Author Comment

by:u2envy1
Comment Utility
I tried this also.....

  var rsPunch1 = (from t in PunchCtx.TimeAtendancePunches
                                orderby t.Tap_DateAndTime_DT descending
                                //join te in PunchCtx.TerminalTypes on t.Tap_Terminal_SI equals te.TrmT_ID_SI
                                join te in PunchCtx.PunchesTerminals on t.Tap_Terminal_SI equals te.Trm_ID_SI
                                where t.Tap_EmployeeNumber_VC16 == empNo
                                select new
                                {
                                    PunchDescription = "T",
                                    Terminal = t.Tap_Terminal_SI,
                                    Date = t.Tap_DateAndTime_DT,
                                    TName = te.TRM_TerminalName_VC15,
                                    EmployeeNo = t.Tap_EmployeeNumber_VC16,
                                    EditedBy = t.Tap_EditedBy_VC20,
                                    Reason = t.Tap_Reason_VC20


                                });

                var rsPunch = (from a in PunchCtx.AccessPunches
                               orderby a.Acp_DateAndTime_DT descending
                               join te in PunchCtx.PunchesTerminals on a.Acp_Terminal_SI equals te.Trm_ID_SI
                               //join t in PunchCtx.TerminalTypes on a.Acp_Terminal_SI equals t.TrmT_ID_SI
                               where a.Acp_EmployeeNumber_VC16 == empNo
                               select new
                               {
                                   PunchDescription = "A",
                                   Terminal = a.Acp_Terminal_SI,
                                   Date = a.Acp_DateAndTime_DT,
                                   TName = te.TRM_TerminalName_VC15,
                                   EmployeeNo = a.Acp_EmployeeNumber_VC16,
                                   EditedBy = a.Acp_EDITEDBY_VC20,
                                   Reason = a.Acp_REASON_VC20

                               });

var rsUnion = rsPunch1.Union(rsPunch);
0
 
LVL 4

Expert Comment

by:novynov
Comment Utility
Have you tried creating a "placeholder" class for your projection (the results of your select)...and done a select new QueryResults{..}? See snippet below.

I'd still like to confirm that the types of each of those anonymous types' properties matches exactly. If the names and types match, you shouldn't have an issue. Granted there could be something else I'm missing...but I would like to confirm this first. That's why I requested that info in my last post. Can you let me know what each of the source types (e.g. t.Tap_Terminal_SI) is...including whether they are nullable or not?
class QueryResults //More descriptive name needed

{

   public string PunchDescription;

   public string Terminal;

   public DateTime Date;

   public string TName;

   public int EmployeeNo;

   public string EditedBy;

   public string Reason;

}

Open in new window

0
 

Author Comment

by:u2envy1
Comment Utility
thx.......
Much appreciated for your help in all this. How can I give you more points that you really deserve.

------------AccessPunch Tabel--------------

CREATE TABLE [dbo].[AccessPunch](
      [Acp_EmployeeNumber_VC16] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Acp_Terminal_SI] [smallint] NULL,
      [Acp_DateAndTime_DT] [datetime] NOT NULL,
      [Acp_REASON_VC20] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Acp_EDITEDBY_VC20] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Acp_DateAndTimeEDITED_DT] [datetime] NULL,
      [Acp_ORIGINALPUNCHDateAndTime_DT] [datetime] NULL,
 CONSTRAINT [PK_Acp_EmployeeNumber_VC16_Acp_DateAndTime_DT] PRIMARY KEY CLUSTERED
(
      [Acp_EmployeeNumber_VC16] ASC,
      [Acp_DateAndTime_DT] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AccessPunch]  WITH CHECK ADD  CONSTRAINT [FK__AccessPun__Acp_E__32AB8735] FOREIGN KEY([Acp_EmployeeNumber_VC16])
REFERENCES [dbo].[Employee] ([Emp_EmployeeNumber_VC16])
GO
ALTER TABLE [dbo].[AccessPunch] CHECK CONSTRAINT [FK__AccessPun__Acp_E__32AB8735]
GO
ALTER TABLE [dbo].[AccessPunch]  WITH CHECK ADD FOREIGN KEY([Acp_Terminal_SI])
REFERENCES [dbo].[Terminal] ([Trm_ID_SI])

-----------------TimeAtendancePunches Table----------

CREATE TABLE [dbo].[TimeAtendancePunches](
      [Tap_EmployeeNumber_VC16] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Tap_DateAndTime_DT] [datetime] NOT NULL,
      [Tap_DptID_SI] [smallint] NULL,
      [Tap_PunchType_SI] [smallint] NOT NULL,
      [Tap_IsInPunch] [bit] NULL,
      [Tap_Terminal_SI] [smallint] NOT NULL,
      [Tap_Reason_VC20] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Tap_EditedBy_VC20] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Tap_DateAndTimeEdited_DT] [datetime] NULL,
      [Tap_OriginalPunchDateAndTime_DT] [datetime] NOT NULL,
      [Tap_MiscCategoryID_SI] [smallint] NULL,
      [Tap_McatDurationHours_SI] [smallint] NULL,
      [Tap_McatDurationMinutes_TI] [tinyint] NULL,
      [Tap_ProcessingCode_TI] [tinyint] NOT NULL,
      [Tap_PayrunCompleted_B] [bit] NOT NULL,
      [Tap_KeyType_B] [bit] NULL,
 CONSTRAINT [PK_Tap_EmployeeNumber_VC16_Tap_DateAndTime_DT] PRIMARY KEY CLUSTERED
(
      [Tap_EmployeeNumber_VC16] ASC,
      [Tap_DateAndTime_DT] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TimeAtendancePunches]  WITH CHECK ADD  CONSTRAINT [FK__TimeAtend__Tap_E__2CF2ADDF] FOREIGN KEY([Tap_EmployeeNumber_VC16])
REFERENCES [dbo].[Employee] ([Emp_EmployeeNumber_VC16])
GO
ALTER TABLE [dbo].[TimeAtendancePunches] CHECK CONSTRAINT [FK__TimeAtend__Tap_E__2CF2ADDF]
GO
ALTER TABLE [dbo].[TimeAtendancePunches]  WITH CHECK ADD  CONSTRAINT [FK__TimeAtend__Tap_M__2FCF1A8A] FOREIGN KEY([Tap_MiscCategoryID_SI])
REFERENCES [dbo].[MiscCategories] ([MCat_ID_SI])
GO
ALTER TABLE [dbo].[TimeAtendancePunches] CHECK CONSTRAINT [FK__TimeAtend__Tap_M__2FCF1A8A]
GO
ALTER TABLE [dbo].[TimeAtendancePunches]  WITH CHECK ADD  CONSTRAINT [FK__TimeAtend__Tap_P__2DE6D218] FOREIGN KEY([Tap_PunchType_SI])
REFERENCES [dbo].[PunchType] ([Pt_ID_SI])
GO
ALTER TABLE [dbo].[TimeAtendancePunches] CHECK CONSTRAINT [FK__TimeAtend__Tap_P__2DE6D218]
GO
ALTER TABLE [dbo].[TimeAtendancePunches]  WITH CHECK ADD  CONSTRAINT [FK__TimeAtend__Tap_T__2EDAF651] FOREIGN KEY([Tap_Terminal_SI])
REFERENCES [dbo].[Terminal] ([Trm_ID_SI])
GO
ALTER TABLE [dbo].[TimeAtendancePunches] CHECK CONSTRAINT [FK__TimeAtend__Tap_T__2EDAF651]


------------------------------TerminalType Table-------------------(Used in the JOIN)

CREATE TABLE [dbo].[TerminalType](
      [TrmT_ID_SI] [smallint] IDENTITY(0,1) NOT NULL,
      [TrmT_Name_VC10] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_TrmT_ID_SI] PRIMARY KEY CLUSTERED
(
      [TrmT_ID_SI] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
0
 
LVL 4

Accepted Solution

by:
novynov earned 500 total points
Comment Utility
OK...I think this may help resolve the issue...Notice the difference in types between the following columns that you use in your query:

- [Acp_Terminal_SI] [smallint] NULL vs [Tap_Terminal_SI] [smallint] NOT NULL
- [Acp_REASON_VC20] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL vs  [Tap_Reason_VC20] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

I think those are the only two...but please confirm. Now...the second one is no big deal...as both of those sql types will map to a System.String in your anonymous type. However, I believe the first one (...Terminal...) is causing grief for the type checker when the anonymous types are generated...as the Acp one will probably generate a property with type int? and the Tap one will probably generate a prop with type int.

So...assuming this is the issue...let me again stress that I believe the two anonymous types need to have properties whose types and names are a match...property by property.

I think you have two options to fix this. Option #1) Use a class like I suggested above...this will ensure that the types between both sides of the union are the same...You'll need to coallesce the nullable int to a non-nullable or vice versa (let me know if you need help doing this)...or Option #2) coallesce them in the select new...and still use anonymous types.

We'll get to the bottom of this yet. Keep me posted.


0
 
LVL 4

Expert Comment

by:novynov
Comment Utility
By the way...I noticed the following...which has little to do with your question:

It appears that your column names are a kind of hungarian notation for indicating the types in the column names. True?

If so...it looks like you have a mismatch:

[Tap_Reason_VC20] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

If I understand your naming convention correctly, the column should be named "Tap_Reason_VC50"...true?

Just an observation in an effort to help.
   
0
 

Author Comment

by:u2envy1
Comment Utility
Thx novynov: Only had time now to fix the problem. It was the nullable types on the Terminal fields.
Much appreciated for all your time & patients.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now