C# Linq SQL select Multiple columns to List

Question

Hi I want to select 4 columns form DB then select to a List

public struct keyData
{

    public int pid;
    public int sid;
    public string proid;
    public string title;
}

Entities dbconn = new Entities();
List<keyData> temRes = (
    from viewData in dbconn.vw1
    join hData in dbconn.tableH
    on new { pid= (int)viewData.pid, proid= viewData.proid}
    equals new { pid= (int)hData .pid, proid= hData .proid}
    into joinSet
    from joinUnit in joinSet.DefaultIfEmpty()
    where joinUnit == null
    select new { pid= (int)viewData.pid, sid= (int)viewData.sid, proid= viewData.proid, Title=viewData.Title }
    ).ToList();

then it says:

Error 3 Cannot implicitly convert

type 'System.Collections.Generic.List<AnonymousType#1>'

to 'System.Collections.Generic.List<hl.Program.keyData>'

thanks

using MarcinJuraszek 's idea

it gives me :

Only parameterless constructors and initializers are supported in LINQ to Entities

MarcinJuraszek · Accepted Answer · 2013-10-23 00:16:17Z

5

That's because your quere return an anonymous type objects. Change it to return keyData instances instead:

    List<keyData> temRes = (
        from viewData in dbconn.vw1
        join hData in dbconn.tableH
        on new { pid= (int)viewData.pid, proid= viewData.proid}
        equals new { pid= (int)hData .pid, proid= hData .proid}
        into joinSet
        from joinUnit in joinSet.DefaultIfEmpty()
        where joinUnit == null
        select new keyData() { pid= (int)viewData.pid, sid= (int)viewData.sid, proid= viewData.proid, Title=viewData.Title }
        ).ToList();

The difference is in select clause. I've added your class name after new keyword.

edited Oct 23, 2013 at 0:16

answered Oct 22, 2013 at 22:54

MarcinJuraszek

126k15 gold badges199 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Benny Ae Over a year ago

then it give me : Only parameterless constructors and initializers are supported in LINQ to Entities.

MarcinJuraszek Over a year ago

Do you have parameterless constructor defined for keyData class?

King King Over a year ago

@BennyAe try adding an empty parameterless constructor to your keyData structure.

Hardik Patel · Accepted Answer · 2014-08-06 11:16:30Z

0

Entities dbconn = new Entities();
List<keyData> temRes = (
    from viewData in dbconn.vw1.ToList()
    join hData in dbconn.tableH.ToList()
    on new { pid= (int)viewData.pid, proid= viewData.proid}
    equals new { pid= (int)hData .pid, proid= hData .proid}
    into joinSet
    from joinUnit in joinSet.DefaultIfEmpty()
    where joinUnit == null
    select new { pid= (int)viewData.pid, sid= (int)viewData.sid, proid= viewData.proid,  Title=viewData.Title }
    ).ToList();

answered Aug 6, 2014 at 11:16

Hardik Patel

3273 silver badges13 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:20:50Z

As it was said you must return keyData instances instead of anonimous type https://stackoverflow.com/a/19529805/1034373. But struct can't have an explicit parameterless constructor so you must either change keyData type to class or add a counstructor

public struct keyData
{
    public keyData(int pid, int sid, string proid, string title)
    {
         this.pid = pid;
         this.sid = sid;
         this.proid = proid;
         this.title = title;
    }
    public int pid;
    public int sid;
    public string proid;
    public string title;
}

and write code like this

List<keyData> temRes = (
    from viewData in dbconn.vw1
    join hData in dbconn.tableH
    on new { pid= (int)viewData.pid, proid= viewData.proid}
    equals new { pid= (int)hData .pid, proid= hData .proid}
    into joinSet
    from joinUnit in joinSet.DefaultIfEmpty()
    where joinUnit == null
    select new { pid= (int)viewData.pid, sid= (int)viewData.sid, proid= viewData.proid, Title=viewData.Title }
    ).ToList().Select(x => new keyData(x.pid, x.sid, x.proid, x.title)).ToList();

Community · Accepted Answer · 2017-05-23 12:20:34Z

0

I have answered similar question here.You can simply do this using SELECT and SELECTMANY https://stackoverflow.com/a/27755340/2764258

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jan 3, 2015 at 13:45

AR M

3333 silver badges7 bronze badges

1 Comment

Gert Arnold Over a year ago

This is a comment, not an answer. The normal procedure would be cast a close vote (duplicate) but I think you haven't got enough points for that yet. By the way, your answer to the other question does not apply here.

Collectives™ on Stack Overflow

C# Linq SQL select Multiple columns to List

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related