CODEWITHSUNDEEP
c#sql-serverasp.net-mvcentity-framework
Zag Gol
Zag Gol

Reputation: 1076

Entity Framework from database - calculate property from another table

In my database I have tables Person and Work that are connected with one Person to many Work with a foreign key PersonId.

These are the tables:

CREATE TABLE [dbo].[Work]
(
        [WorkId] [int] IDENTITY(1,1) NOT NULL,
        [PersonId] [int] NOT NULL,
        [KollelStatusId] [tinyint] NOT NULL,
        [Job] [nvarchar](50) NULL,
        [Mascoret] [int] NULL,

        CONSTRAINT [PK_Work] 
           PRIMARY KEY CLUSTERED ([WorkId] ASC)
                   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                         ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Work] WITH CHECK 
   ADD CONSTRAINT [FK_Work_Person] 
   FOREIGN KEY([PersonId]) REFERENCES [dbo].[Person] ([PersonId])

CREATE TABLE [dbo].[Person]
(
        [PersonId] [int] IDENTITY(1,1) NOT NULL,
        [FamilyId] [int] NULL,
        [FirstName] [nvarchar](50) NOT NULL,
        [LastName] [nvarchar](50) NULL,
        [DateOfBirth] [smalldatetime] NOT NULL,
        [TeudatZehut] [varchar](9) NULL,
        [Phone] [varchar](15) NULL,
        [Email] [varchar](100) NULL,
        [Issue] [nvarchar](2000) NULL,

        CONSTRAINT [PK_KupaPerson] 
           PRIMARY KEY CLUSTERED ([PersonId] ASC)
                   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                         ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I need to show the persons in an ASP.NET MVC view. but instead of PersonId, I want to show the Job string.

How to do it with Entity Framework generated from database?

Thanks?

Upvotes: 0

Views: 642

Answers (1)

Marcus Höglund
Marcus Höglund

Reputation: 16811

If you got the personId in the value _personId you could do like this:

var listOfJobs = db.Work.Where(a=>a.PersonId == _personId).Select(a=>a.Job).ToList();

This will return a list of the jobs that person is linked to.

If you only got the name or any other value from the person model you could do something like this:

var listOfJobs = db.Work.Where(a=>a.Person.LastName == _lastName).Select(a=>a.Job).ToList();

This will return a list of the jobs that all persons with the LastName is equal to _lastName value.

If you want to get values from both tables you could JOIN the entities:

var listOfObjects = from w in db.Work
join p in db.Person on p.PersonId equals w.PersonId
where w.PersonId == _personId
select w.Job, p.FirstName;

If you want all persons jobs from the db, just remove the where statement from above like:

var listOfObjects = from w in db.Work
join p in db.Person on p.PersonId equals w.PersonId 
select w.Job, p.FirstName;

This will generate one query to fetch all persons jobs.

If you need more values from the models, just extend the select statement with more values like:

select w.Job, p.FirstName, p.LastName, w.WorkId, w.Mascoret

Upvotes: 1

Related Questions