ASP.NET MVC get specific columns from table in EF

Question

I have a table with below columns.

CREATE TABLE [dbo].[tbl_QuizQue](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [question] [nvarchar](max) NOT NULL,
    [opt1] [nvarchar](50) NOT NULL,
    [opt2] [nvarchar](50) NOT NULL,
    [opt3] [nvarchar](50) NOT NULL,
    [ans] [nvarchar](50) NOT NULL
    )

I am trying to get a random record from this table, but only specific columns Like id, question, opt1, opt2, opt3. Present I am getting random record but it is fetching all columns. I tried below code for fetching a record with specific columns

dbUserEntities obj = new dbUserEntities();
    // GET api/values
    public IEnumerable<tbl_QuizQue> Get()
    {
        var result = obj.tbl_QuizQue.OrderBy(r => Guid.NewGuid()).Select(o => new { o.id, o.question, o.opt1, o.opt2, o.opt3 });
        return result;
        }

Here, i am getting below error while return result;

Cannot implicitly convert type 'System.Linq.IQueryable<AnonymousType#1>' to 
        'System.Collections.Generic.IEnumerable<studentQuiz.tbl_QuizQue>'. An explicit conversion exists (are you missing a cast?)

Help me where I am doing wrong.

It'd be simpler just to fetch the entire entity. Your columns don't look like one of them is huge enough to justify the extra complexity. — Matti Virkkunen, Jul 03 '16 at 15:00
The value of `result` is a collecton of anonymous object (created using `.Select()`), not a collction of `tbl_QuizQue` object, hence the exception. — , Jul 03 '16 at 23:21

score 2 · Answer 1 · answered Jul 03 '16 at 15:21

2

I'd do this:

public IEnumerable<tbl_QuizQue> Get()
{
    var result = obj.tbl_QuizQue.OrderBy(r => Guid.NewGuid())
        .Select(o => new { o.id, o.question, o.opt1, o.opt2, o.opt3 })
        .AsEnumerable()
        .Select(q => new tblQuizQue {q.id, q.question, q.opt1, q.opt2, q.opt3);
    return result;
}

By first getting back just the columns you are interested in, you can constrain the query. You can then map them into the object you are interested in.

answered Jul 03 '16 at 15:21

Jeff Siver

7,434
30
32

Thanks for reply. But getting this error **Cannot initialize type 'studentQuiz.tbl_QuizQue' with a collection initializer because it does not implement 'System.Collections.IEnumerable'** – Nagarjuna Reddy Jul 03 '16 at 15:26
What does tbl_QuizQue look like? – Jeff Siver Jul 03 '16 at 15:27
You can see CREATE script in the question for tbl_QuizQue . – Nagarjuna Reddy Jul 03 '16 at 15:29
That's the table. What about the c# class declaration? That's what is behind the error you mentioned. I suspect your return type of IEnumerable is incorrect. – Jeff Siver Jul 03 '16 at 15:39
C# code : **public IEnumerable Get() { var result = obj.tbl_QuizQue.OrderBy(r => Guid.NewGuid()) .Select(o => new { o.id, o.question, o.opt1, o.opt2, o.opt3 }) .AsEnumerable() .Select(q => new tbl_QuizQue {q.id, q.question, q.opt1, q.opt2, q.opt3}); return result; }** – Nagarjuna Reddy Jul 03 '16 at 15:46

Robert Moskal · Answer 2 · 2016-07-03T15:42:37.833

0

Change the return type of the Get function to

IEnumerable<dynamic>

and your code will execute.

As for whether it's a good idea, I guess you have to defer to the customs of the c# tribe.

I'd be tempted to use it all the time.

edited Jul 03 '16 at 15:42

answered Jul 03 '16 at 15:03

Robert Moskal

21,737
8
62
86

It is working in REST Client where I am getting the record with specific columns. But, in Chrome I am getting error. **Type '<>f__AnonymousType3`5[System.Int32,System.String,System.String,System.String,System.String]' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute. If the type is a collection, consider marking it with the CollectionDataContractAttribute. See the Microsoft .NET Framework documentation for other supported types.** – Nagarjuna Reddy Jul 03 '16 at 15:09
1

I don't think using `dynamic` here is very good architecture. Dynamic is a big can of worms to open just like that, you lose type safety for one. – Matti Virkkunen Jul 03 '16 at 15:10
That's another question. I'm surprised it works in the rest client and fails in the browser. Perhaps you aren't setting the right content type. Check this out: http://stackoverflow.com/questions/331976/how-do-i-serialize-a-c-sharp-anonymous-type-to-a-json-string – Robert Moskal Jul 03 '16 at 15:15
In the browser, I am getting results in XML format and JSON in REST Client. – Nagarjuna Reddy Jul 03 '16 at 15:18
So that's your new question. How do you serialize a dynamic object as XML. – Robert Moskal Jul 03 '16 at 15:20
Using dynamic is just a hack to get this working, you shouldn't do that unless you have a good reason. – DavidG Jul 03 '16 at 15:32

ASP.NET MVC get specific columns from table in EF

2 Answers2