Create a UNION ALL between two tables in LINQ

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

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

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

Select allOpen in new window

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


                                    });

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.

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.

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?

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

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

Select allOpen in new window

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]

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.
   

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.