asked on

LINQ Union two table MVC

We are updating our website and now have two set of members. One set of those who have registered and one set of those historical members who have yet to re-register. I am looking for the ability to merge(union) those two tables for a search functionality however I am struggling with the union.

In SQL

select  member_id, ssn
from member a 
union 
select  member_id, ssn
from member_historical

Open in new window

LINQ

     var union = (from members in db.members
                    select new
                    {
                        members.member_id,
                        members.ssn
                    }
                ).Union
                    (from member_historical in db.member_historical
                     select new
                    {
                        member_id = member_historical.member_id,
                        ssn = member_historical.ssn
                    }
                );

Open in new window

When I return view(union) an error: Argument type 'system.linq.Iquerable<member_id;int ssn:string}>' is not assignable to model type of 'system.collections.generic.IEnumberable<Data.member>'

Tried to cast: return View((IEnumerable<member>) union);

I'm a little lost, any help or guidance is appreciated.

ASKER CERTIFIED SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lovettjay

ASKER

Thanks for your help. The union joins make sense still having some issues with the model in the view. I know I need to update this line of code in the view:

@model IEnumerable<Data.member>

Open in new window

  List<member> m = new List<member>(from i in Enumerable.Range(1, 5) select new member() { member_id = i, ssn = string.Format("ssn{0}", i) });
            List<member_historical> mH = new List<member_historical>(from i in Enumerable.Range(6, 5) select new member_historical() { member_id = i, ssn = string.Format("ssn{0}", i) });

            var union = (from item in m select new { item.member_id, item.ssn}).Union(from item in mH select new { item.member_id, item.ssn});
            foreach (var x in union)
            {
                return View(union);
            }

Open in new window

Error Argurment type 'System.:Linq.IQueryable<{member_id:int, ssn:string}>' is not assignable to model type 'System.Collections.Generic<Data.member>'

    var union1 = (from ber in db.members
                    select new
                    {
                        MemberID = ber.member_id,
                        SSN = ber.ssn
                    })
                .Union(from ber in db.member_historical
                    select new
                    {
                        MemberID = ber.member_id,
                        SSN = ber.ssn
                    }
                );

Open in new window

Error Argurment type 'System.:Linq.IQueryable<{MemberID:int, SSN:string}>' is not assignable to model type 'System.Collections.Generic<Data.member>'

    var union2 = (from z in db.members
                    select new
                    {
                        z.member_id,
                        z.ssn
                    })
                .Union(from z in db.member_historical
                    select new
                    {
                        z.member_id,
                        z.ssn
                    }
                );

Open in new window

Argument type 'System.Linq.IQueryable<{member_id:int, ssn:string}>' is not assignable to model type 'System.Collections.Generic<Data.member>'

it_saige

What is the implementation for member?

-saige-

lovettjay

ASKER

The was the generic model that was added with the creation of this view

lovettjay

ASKER

Thanks for your help