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

Question

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.

You can just remove properties you don't need in EF designer: create model from database and drop what you don't need — JackNova, Sep 29 '11 at 14:02
@JackNova It won't let me delete the properties that can't be null, and there's a lot of those — Rachel, Sep 29 '11 at 14:05

score 4 · Answer 1 · answered Sep 30 '11 at 17:24

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();

score 3 · Answer 2 · answered Sep 29 '11 at 13:45

3

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.

answered Sep 29 '11 at 13:45

Petar Petkov

1,429
1
13
21

I tried adding the table and deleting all the columns except the ones I need, but it makes me keep all columns which cannot be null, and there's quite a few of those. I didn't like the View idea because I don't really know how to easily update a view, but I suppose I can find out... – Rachel Sep 29 '11 at 14:02

score 3 · Accepted Answer · answered Sep 29 '11 at 14:09

3

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.

answered Sep 29 '11 at 14:09

E.J. Brennan

45,870
7
88
116

I think this is the way I am going to go. I had hoped there was an alternative that would let me manipulate the table directly, but it doesn't look like it. – Rachel Sep 29 '11 at 14:33

score 2 · Answer 4 · edited May 27 '20 at 04:28

2

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.

edited May 27 '20 at 04:28

John McDonald

1,790
13
20

answered Sep 30 '11 at 04:09

Bryan Hong

1,483
13
28

That would work great, if the database I'm working with didn't have so many required fields. EF doesn't let me delete fields from an Entity if they cannot be null, and a large number of the fields in the tables I'm working with can't be null. +1 anyways though for a nice solution. – Rachel Sep 30 '11 at 12:13
oh bummer though, I wonder if EF would allow the deletion of a required field if it had a default value setting. Haven't tried it though. I also saw this link which looks to be similar, however there's not much of a viable solution either: http://stackoverflow.com/questions/737520/entity-framework-ignore-columns – Bryan Hong Sep 30 '11 at 12:25
@Rachel, actually, you can do this, it's just that the designer doesn't really support it directly. You have to manually edit the EDMX and remove the fields from SSDL, MSL, and CSDL. Note that this is different than Table Splitting. – Craig Stuntz Sep 30 '11 at 17:28
@CraigStuntz Thanks, I actually noticed later on that it appears I can do it if I manually edit the XML files, however the changes get erased anytime I update the models from the database. I've already gone down the path of using Stored Procedures, however I will keep this in mind for the future if I ever need to do so – Rachel Sep 30 '11 at 17:36
1

@Rachel, we work around this by using a separately maintained DB for EDMX generation. It's like the "real" DB except that it lacks certain columns. – Craig Stuntz Sep 30 '11 at 17:38

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

4 Answers4