Entity Framework Core database-first selecting specific columns

Question

    public Tbl016 CheckLoginCredentials(string UserName, string Password)
    {
        return
            context.Agents
            .Where(x => x.LoginName == UserName && x.LoginPassword == Password)
            .Select(x => new Tbl016
            {
                LoginName = x.LoginName,
                LoginPassword = x.LoginPassword
            })
            .FirstOrDefault();
    }

I'm developing an Entity Framework Core API using database-first approach and using a repository design pattern. In the above function I'm trying to retrieve specific columns from the database, but unfortunately, when I run the code, the response body brings data from all the columns in the table.

What I've tried so far is the .Select new expression but it is not working.

Welcome to Stack Overflow. Please take the tour to learn how Stack Overflow works and read How to Ask on how to improve the quality of your question. Then edit your question to include your source code as a working minimal reproducible example, which can be compiled and tested by others. Also see meta.stackoverflow.com/questions/271055/… for SQL related questions. Please do not upload images of code/errors when asking a question. — Progman
– Progman, Commented Nov 5, 2021 at 20:56
Unless I'm misunderstanding, you seem to be attempting to use Entity Framework the wrong way. Entity Framework by default updates its context whenever accessing variables from the dbcontext of model from the database if it doesn't already have them, and that's one of its best features. If you only want singular columns from the database you should run your own sql with FromSqlRaw to select your own columns. If you only want to USE or pass back the two variables you are, I recommend making a ViewModel class for those variables and setting them to the dbcontext model data you're pulling from — gen
– gen, Commented Nov 5, 2021 at 20:59
@Gen -I'm using reverse engineering EF core because I'm building an API for a software with an existing Database. First of All and unfortunately I don't have a clear picture of EF Core "ViewModel" is it similar to what we do in Xamarin.Forms?. Secondly If I created such a class I might be finding my self in a situation where I need columns other than these two. Shall I create a new "ViewModel" or just one is enough in which I include every single property in the table? — Mohammad Radwan
– Mohammad Radwan, Commented Nov 5, 2021 at 21:21
@Gen Using Select() is fine when using Entity Framework. LINQ-to-EF even have support for Select() to fetch only the columns needed/requested. There is no need to build your own SQL query with FromSqlRaw(). — Progman
– Progman, Commented Nov 5, 2021 at 21:26
it depends on the usage to be honest, if you make a ViewModel that contains those properties you can always add more to it and assign those variables to the class pretty easily. However, if your only point in making a separate class to put the data into is for 'speed' or for 'less SQL' or 'less queries', don't bother. Entity Framework has already updated the context for the item you are accessing, so all of the variables attached to that object are ALREADY updated by the time you're making the viewmodel — gen
– gen, Commented Nov 5, 2021 at 21:26

mxmissile · Accepted Answer · 2021-11-05 22:13:19Z

2

You can return an anonymous object instead:

.Select(x => new {
    x.LoginName,
    x.LoginPassword
}).FirstOrDefault();

This should only select -> send the 2 columns.

answered Nov 5, 2021 at 22:13

mxmissile

11.7k6 gold badges57 silver badges85 bronze badges

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

1 Comment

Mohammad Radwan Over a year ago

Yeah.. It worked for me. Of course after I changed the return type of the method. Thanks a lot for your help.

Mohammad Radwan · Accepted Answer · 2021-11-05 22:15:02Z

0

Thanks to @mxmissile the problem is solved I changed the return type of the method to dynamic instead of (an Object of the table). then I returned data anonymously in the .Select() method The full code is:

public dynamic CheckLoginCredentials(string UserName, string Password)
{
    return context.Agents
        .Where(x => x.LoginName == UserName && x.LoginPassword == Password)
        .Select(x => new {
            x.LoginName,
            x.LoginPassword
        })
        .FirstOrDefault();
}

answered Nov 5, 2021 at 22:15

Mohammad Radwan

451 silver badge7 bronze badges

Collectives™ on Stack Overflow

Entity Framework Core database-first selecting specific columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related