Solved

LINQ - using union

Posted on 2015-02-02
12
93 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Solution for warm standby SQL server 20 38
Returning data in a different format 8 46
datetime in sql 6 25
What query can i write to find where a function is 4 21
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

24 Experts available now in Live!

Get 1:1 Help Now