Entity framework - select specific columns

Question

this is my user model or class

 public int UserID { get; set; }
        public string UserName { get; set; }
        public string UserPassword { get; set; }
        public int CompanyID { get; set; }
        public int BranchID { get; set; }
        public bool RecordState { get; set; }


        public virtual Branch Branch { get; set; }
    public virtual Company Company { get; set; }

and this is my company class

public int CompanyID { get; set; }
        public string CompanyName { get; set; }
        public string CompanyShortName { get; set; }
        public string CompanyAddress { get; set; }
        public string CompanyPhone { get; set; }
        public string CompanyEmail { get; set; }
        public string CompanyFax { get; set; }
        public bool RecordState { get; set; }
        public virtual List<Branch> Branches { get;  set; }
        public virtual List<Customer> Customers { get;  set; }
        public virtual List<Market> Markets { get;  set; }
        public virtual List<Seller> Sellers { get;  set; }
        public virtual List<User> Users { get;  set; }

this my [WebMethod]

 public User getUser(int id)
        {
            User user = db.Users
                .Include(c => c.Company)
                .Where(i => i.UserID == id)
                .FirstOrDefault<User>();

            Company company = db.Companies
                .Where(i => i.CompanyID == user.CompanyID )
                .FirstOrDefault<Company>();

            company.Branches = null;
            company.Customers = null;
            company.Markets = null;
            company.Sellers = null;
            company.Branches = null;
            company.Users = null;

            user.Company = company;

            return user;
        }

My method is long beacuse I want to avoid circular reference but I think my steps is not good and it takes many steps I want to know i there any why that I can get the company inside user with one query and it should also return an object type user because ? i'm really sorry for my bad English

jmcilhinney · Accepted Answer · 2014-03-13 02:10:27Z

1

All you need is the first and the last line of that method. The rest is completely redundant. By specifying the Include path you are already getting the company, so there's no need to get that separately. By not specifying any other include paths, you are already not getting any more related records, so setting all those properties to null is pointless.

answered Mar 13, 2014 at 2:10

jmcilhinney

54.9k5 gold badges30 silver badges51 bronze badges

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

5 Comments

user3367149 Over a year ago

When I just use the first line I got this error System.InvalidOperationException: There was an error generating the XML document. ---> System.InvalidOperationException: A circular reference was detected while serializing an object of type GraduateServer.Models.User.

jmcilhinney Over a year ago

You probably need to turn lazy loading off in your EF context. As suggested in another comment, EF is designed for lazy loading, which means that related entities will be retrieved when accessed. When passing objects from a web service though, you know exactly what related entities you need up front so you should turn lazy loading off and use Include calls to be explicit about what related records to retrieve. After creating your DbContext, do this: context.Configuration.LazyLoadingEnabled = false;

user3367149 Over a year ago

Fisrt it's off, second would please write a simple code what I want is when I looking after user by ID I want Include company but I don't wanna get all the columns of Company I wanna choose what I want, sorry for my bad english

jmcilhinney Over a year ago

I don't know what to tell you. That exact type of thing works for me.

user3367149 Over a year ago

msdn.microsoft.com/en-us/data/jj574232#explicit I followed and I write this var user = db.Users; db.Entry(user).Reference("Company").Load(); but still nothing work

Michael Richardson · Accepted Answer · 2014-03-13 02:02:45Z

0

You're trying to do way too much. I'm pretty sure your code won't actually do what you want. This is all you need. (I'm assuming that db is a property or field that's already been instantiated in your class.)

public User getUser(int id)
{
    return db.Users.Find(id);
}

Once you return the User, you can get the company like this

var user = getUser(25);
var userCompany = user.Company;

answered Mar 13, 2014 at 2:02

Michael Richardson

4,3022 gold badges34 silver badges51 bronze badges

6 Comments

user3367149 Over a year ago

I want to get the related object in this case I meant company in ur case company is null

Michael Richardson Over a year ago

If company is null then you need to set it and persist the changes to the database.

user3367149 Over a year ago

I think that because that u don't set Include this method gave u the related object

Michael Richardson Over a year ago

Include is only needed for eager loading. Entity Framework is designed for lazy loading, which is the way that we're doing it here.

user3367149 Over a year ago

System.InvalidOperationException: There was an error generating the XML document. ---> System.InvalidOperationException: A circular reference was detected while serializing an object of type GraduateServer.Models.User.

|

Collectives™ on Stack Overflow

Entity framework - select specific columns

2 Answers 2

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related