CODEWITHSUNDEEP
linqlinq-to-sqlselect
makerofthings7
makerofthings7

Reputation: 61463

How can I merge two outputs of two Linq queries?

I'm trying to merge these two object but not totally sure how.. Can you help me merge these two result objects? 

 //
 // Create Linq Query for all segments in "CognosSecurity"
 //
 var userListAuthoritative = (from c in ctx.CognosSecurities
                             where (c.SecurityType == 1 || c.SecurityType == 2)
                             select new {c.SecurityType, c.LoginName , c.SecurityName}).Distinct();
 //
 // Create Linq Query for all segments in "CognosSecurity"
 //
 var userListAuthoritative3 = (from c in ctx.CognosSecurities
                              where c.SecurityType == 3 || c.SecurityType == 0
                              select new {c.SecurityType , c.LoginName }).Distinct();

I think I see where to go with this... but to answer the question the types of the objects are int, string, string for SecurityType, LoginName , and SecurityName respectively

If you're wondering why I have them broken like this is because I want to ignore one column when doing a distinct. Here are the SQL queries that I'm converting to SQL.

  select distinct SecurityType, LoginName, 'Segment'+'-'+SecurityName
FROM [NFPDW].[dbo].[CognosSecurity]
where SecurityType =1

  select distinct SecurityType, LoginName, 'Business Line'+'-'+SecurityName
FROM [NFPDW].[dbo].[CognosSecurity]
where SecurityType =2

   select distinct SecurityType, LoginName, SecurityName
FROM [NFPDW].[dbo].[CognosSecurity]
where SecurityType in (1,2)

Upvotes: 3

Views: 3813

Answers (5)

StriplingWarrior
StriplingWarrior

Reputation: 156524

I assume that you want to keep the results distinct:

var merged = userListAuthoritative.Concat(userListAuthoritative3).Distinct();

And, as Mike Q pointed out, you need to make sure that your types match, either by giving the anonymous types the same signature, or by creating your own POCO class specifically for this purpose.

Edit

If I understand your edit, you want your Distinct to ignore the SecurityName column. Is that correct?

var userListAuthoritative = from c in ctx.CognosSecurities
                            where new[]{0,1,2,3}.Contains(c.SecurityType)
                            group new {c.SecurityType, c.LoginName, c.SecurityName}
                                by new {c.SecurityType, c.LoginName}
                            select g.FirstOrDefault();

Upvotes: 1

Broomandan
Broomandan

Reputation: 21

Try below code, you might need to implement IEqualityComparer<T> in your ctx type.

var merged = userListAuthoritative.Union(userListAuthoritative3);

Upvotes: 0

Kirk Woll
Kirk Woll

Reputation: 77546

Edit: This assumed they were of the same type -- but they're not. 

userListAuthoritative.Concat(userListAuthoritative3);

Upvotes: 0

David Ly
David Ly

Reputation: 31586

I'm not exactly sure what you mean by merge, since you're returning different (anonymous) types from each one. Is there a reason the following doesn't work for you?

var userListAuthoritative = (from c in ctx.CognosSecurities
                         where (c.SecurityType == 1 || c.SecurityType == 2 || c.SecurityType == 3 || c.SecurityType == 0)
                         select new {c.SecurityType, c.LoginName , c.SecurityName}).Distinct();

Upvotes: 0

Mike Q
Mike Q

Reputation: 23229

You can't join these because the types are different (first has 3 properties in the resulting type, second has two).

If you can tolerate putting a null value in for the 3rd result of the second query this will help. I would then suggest you just do a userListAuthoritative.concat(userListAuthoritative3 ) BUT I think this will not work as the anonymous types generated by the linq will not be of the same class, even tho the structure is the same. To solve that you can either define a CustomType to encapsulate the tuple and do select new CustomType{ ... } in both queries or postprocess the results using select() in a similar fashion.

Acutally the latter select() approach will also allow you to solve the parameter count mismatch by implementing the select with a null in the post-process to CustomType.

EDIT: According to the comment below once the structures are the same the anonymous types will be the same.

Upvotes: 5

Related Questions