• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4433
  • Last Modified:

Create a UNION ALL between two tables in LINQ

How do I create a UNION ALL between two tables in LINQ. C#
0
u2envy1
Asked:
u2envy1
  • 7
  • 6
1 Solution
 
u2envy1Author Commented:
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
 
u2envy1Author Commented:
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
 
novynovCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
u2envy1Author Commented:
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
 
novynovCommented:
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
 
u2envy1Author Commented:
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
 
novynovCommented:
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
 
u2envy1Author Commented:
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
 
novynovCommented:
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
 
u2envy1Author Commented:
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
 
novynovCommented:
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
 
novynovCommented:
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
 
u2envy1Author Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now