CODEWITHSUNDEEP
c#entity-framework
Rachel
Rachel

Reputation: 132618

Can I get Entity Framework to work only part of a table?

I need to connect to an existing database table and allow users to update a few columns. The thing is, this database table is part of our 3rd party software and contains over 150 columns, and I only need to access about 5 of them.

Is there a way, without using views, to get Entity Framework to select and update only a few columns?

I suppose I could pull down the entire record, but I also have a 2nd table I need to connect to and I had wanted to break that table out into multiple entities, because our software vendor, in their infinite wisdom, decided to put multiple unrelated bits of information into a single table.

Upvotes: 3

Views: 4326

Answers (4)

Bryan Hong
Bryan Hong

Reputation: 1483

The Entity Framework has a feature called Table Splitting. I found the steps here: Table Splitting in Entity Framework

You can split into two entities in your EF model so that one entity would only have the 5 columns you need. I have tried this before to help me avoid retrieving all of the data from a table.

Upvotes: 2

Craig Stuntz
Craig Stuntz

Reputation: 126587

The options mentioned so far will work, but there is an easier solution which requires neither new DB schema nor changing your model.

To select only a few columns, just project:

var smallResultSet = from b in Context.BigHonkinTable
                     where b.Id == someId
                     select new
                     {
                         This = b.This,
                         That = b.That
                     };

Check the generated SQL.

To update, use a stub:

var stub = new BigHonkinEntitiy { Id = someId };
Context.AttachTo("BigHonkinTable", stub);
// important: Mutate stub *after* attaching
stub.This = "Something else";
Context.SaveChanges();

Upvotes: 4

E.J. Brennan
E.J. Brennan

Reputation: 46879

You could do your updates thru a stored procedure mapped into EF. The SP can act on just those columns that you want; you could also use an SP for the select if you would like to cut down on the number of columns pulled down to your app.

Upvotes: 3

Petar Petkov
Petar Petkov

Reputation: 1479

The view would be the best decision here. Although it is possible to create the class and then delete 147 properties and be left with the ones you actually require. Be aware that you will need to keep the primary key in all classes! In addition inserts might not work due to the fact that some of the columns that are left out might not allow nulls which would result in an sql exception.

Upvotes: 3

Related Questions