Solved

LINQ - using union

Posted on 2015-02-02
12
96 Views
Last Modified: 2015-02-17
I have the following Transact SQL query using a union. I need some samples as to how this would look in LINQ.
 internaltional_zip does not have id.
 
 
SELECT transnoid, zip from domestic_zip
UNION
SELECT '' as transnoid, zip from internaltional_zip
0
Comment
Question by:VBdotnet2005
  • 6
  • 5
12 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40584856
Hi VBdotnet2005;

Have you tried something like this?

Dim result = (From CS In ctx.tbl_zipcode_assignment
              Select CS.Zip).Distinct()

Open in new window

0
 

Author Comment

by:VBdotnet2005
ID: 40584867
Hi Fernando,

I changed my question a few minutes ago.
0
 

Author Comment

by:VBdotnet2005
ID: 40584869
I would like to know on how to do the following in LINQ instead.
SELECT transnoid, zip from domestic_zip
UNION
SELECT '' as transnoid, zip from internaltional_zip
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 62

Accepted Solution

by:
Fernando Soto earned 250 total points
ID: 40584988
Hi VBdotnet2005;

OK then, this will work as long as in both the select clause the order of the fields name are in the same order and data type.

Dim results = (From z In Context.domestic_zip _
               Select z.transnoid, z.zip) _
              .Union( _
              (From z In Context.internaltional_zip _
               Select z.transnoid, z.zip))

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40584991
Did you also change the topic areas?
0
 

Author Comment

by:VBdotnet2005
ID: 40585178
My problem is there is no transnoid in internaltional_zip .
0
 

Author Comment

by:VBdotnet2005
ID: 40585179
What would you suggest to do?
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40585297
To your statements, "My problem is there is no transnoid in internaltional_zip ." and "What would you suggest to do?", well I can only go by what you stated and in your SQL statement, you state that you have two columns in both tables you want to return, one being  transnoid and the other being zip. If you are stating that you do not have transnoid how can I suggest something if I do not know what is available or what your requirements are.
0
 
LVL 14

Assisted Solution

by:Lokesh B R
Lokesh B R earned 250 total points
ID: 40585806
Hi,

Please find the sample code in LINQ for UNION

        public class DomesticZip
        {
            public int? transnoid { get; set; }
            public int Zip { get; set; }
        }

List<DomesticZip> domestic = new List<DomesticZip>();
            domestic.Add(new DomesticZip() { transnoid = 1, Zip = 50001 });
            domestic.Add(new DomesticZip() { transnoid = 2, Zip = 50002 });
            domestic.Add(new DomesticZip() { transnoid = 3, Zip = 50003 });
            domestic.Add(new DomesticZip() { transnoid = 4, Zip = 50004 });

            List<DomesticZip> international = new List<DomesticZip>();
            international.Add(new DomesticZip() { Zip = 60001 });
            international.Add(new DomesticZip() { Zip = 60002 });
            international.Add(new DomesticZip() { Zip = 60003 });
            international.Add(new DomesticZip() { Zip = 60004 });

            var query = domestic.Select(c => new DomesticZip { transnoid = c.transnoid, Zip = c.Zip });

            var query1 = international.Select(c => new DomesticZip { transnoid = c.transnoid, Zip = c.Zip });

           var output = query.Union(query1);

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40585903
@Lokesh B R;

The authors resent statement is that transonic is not a member of the table.
0
 

Author Comment

by:VBdotnet2005
ID: 40601781
thank you
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40602190
Was your question answered?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

785 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