CODEWITHSUNDEEP
c#entity-framework
David
David

Reputation: 6111

DbContext.SqlQuery Return Limited Columns

Working in C# .NET Framework 4.5.2.

I have a table that has 60+ columns, some of which contain sensitive data. What I would like to do is return just the Id and Email columns so that I don't have to worry about requesting the sensitive data and so I've setup the following:

var contact = myDatabase.Resources.SqlQuery("SELECT Id, Email FROM Resources WHERE Email = @email", new SqlParameter("@email", registration.Contact.Email))
                                  .Select(c => new { c.Id, c.Email })
                                  .SingleOrDefault();

What I would expect to happen is that an anonymous object would be returned with Id and Email properties, but what is actually happening is that I'm getting the following error:

A member of the type, 'Origin', does not have a corresponding column in the data reader with the same name.

Where Origin just so happens to be the first column name in my data table that I'm not returning.

Am I limited to returning everything or is there something that I can do to only request for the ID and Email?

Upvotes: 1

Views: 246

Answers (1)

vendettamit
vendettamit

Reputation: 14677

The DbSet<TEntity>.SqlQuery() executes the SQL query only for the table which is mapped with the specified entity (e.g. DbSet<Student>.SqlQuery() only returns the result from the corresponding Students table and not from any other table and it tries to map all the columns from the result. see more.

You can either use 'Select * from ..." on DBSet<>.SqlQuery() method or use context.Database.SqlQuery().

Upvotes: 3

Related Questions