Get value for each of array from another table in Web Api LINQ-Asp

Question

I have a 'Skill' table where i store skills. And in 'Job' table i store all required skill when post job lile UpWork. Employeers have checkbox to select all required skills. But i store skillID like: 1,5,6,8 in job table. When i retrieve the job details, i want to get name of the all skills because i want to show SkillName with other details of the Job from job table. My Web Api:

 public object GetJobDetails()
    {

        var data = (from j in db.Jobs
                                 where j.Preference == 2
                                 select new
                                 {
                                   j.JobTitle,
                                   j.JobID,
                                   j.Budget,
                                   j.Deadline,
                                   j.Employeer,
                                   j.JobDetails,
                                   j.PublishDate,
                                   j.ReqSkill,
                                   Category = (from gg in db.Categories where gg.CategoryID == j.Category select gg.CategoryName).FirstOrDefault()

                                 }).ToList();


        return data.AsEnumerable();
    }

the "j.ReSkill" response with like- 1,5,6,8 for each job. How can get SkillName instead of ID- like 'PHP','WordPress','ASP' with all other field. Or, should i store SkillName instead of ID at the time of job post?

How can i make LINQ query to get this? Thanks in advance.

Edited 18-04-17

in live server this code doesn't work but in localhost!!!!

[HttpGet]
    [Route("api/JobApi/BrowseJobs/")]
    public object BrowseJobs()
    {

        var skills = db.Skills.ToDictionary(d => d.SkillID, n => n.SkillName);

        var jobData = (from j in db.Jobs where j.Preference==2
                       //from cj in j.ClosedJobs.DefaultIfEmpty() 
                       join cj in db.ClosedJobs.DefaultIfEmpty()
                       on j.JobID equals cj.JobID into closedJob
                       where !closedJob.Any()
                       join c in db.Categories on j.Category equals c.CategoryID

                       join jobContract in
                           (
                               from appliedJob in db.AppliedJobs.DefaultIfEmpty()
                               from offer in appliedJob.JobOffers.DefaultIfEmpty() 
                               from contract in db.Contracts.DefaultIfEmpty()
                               select new { appliedJob, offer, contract }
                               ).DefaultIfEmpty()
                       on j.JobID equals jobContract.appliedJob.JobID into jobContracts
                       where !jobContracts.Any(jobContract => jobContract.contract.CompletedDate != null)

                       select new
                       {

                           JobTitle = j.JobTitle,
                           JobID = j.JobID,
                           ReqSkillCommaSeperated = j.ReqSkill,
                           Category = c.CategoryName,
                           Budget=j.Budget,
                           Deadline=j.Deadline, 
                           JobDetails=j.JobDetails,
                           PublishDate=j.PublishDate,
                           TotalApplied=(from ap in db.AppliedJobs where j.JobID == ap.JobID select ap.AppliedJobID).DefaultIfEmpty().Count()

                       }).AsEnumerable()
            .Select(x => new
            {
                JobID = x.JobID,
                JobTitle = x.JobTitle,
                Category = x.Category,
                Budget = x.Budget,
                Deadline = x.Deadline,
                JobDetails = x.JobDetails,
                PublishDate = x.PublishDate,
                SkillNames = GetSkillName(x.ReqSkillCommaSeperated, skills),
                TotalApplied = (from ap in db.AppliedJobs where x.JobID == ap.JobID select ap.AppliedJobID).DefaultIfEmpty().Count()
            }).ToList();


        return jobData.AsEnumerable();
    }



    private string GetSkillName(string reqSkill, Dictionary<int, string> skills)
    {
        if (reqSkill == null) return string.Empty;
        var skillArr = reqSkill.Split(',');
        var skillNameList = skillArr.Select(skillId => skills[Convert.ToInt32(skillId)])
                                    .ToList();
        return String.Join(",", skillNameList);
    }

You really should change this into a decent many-to-many association, i.e. with a JobSkill junction table. Now you have no referential integrity whatsoever and working with this code is very inconvenient -- as you already found out. — Gert Arnold
– Gert Arnold, Commented Jan 1, 2017 at 0:31

Shyju · Accepted Answer · 2016-12-31 19:13:31Z

With your current schema structure, i suggest you to read all the skill ids and names to a dictionary and loop through each job in the job collection and get the ReqSkill, Use string.Split method to get individual skillId and get the corresponding name from our skill dictionary.

Something like this

var skills = db.Skills.ToDictionary(d => d.Id, n => n.Name);
var jobData = (from j in db.Jobs
                join c in db.Categories on j.CategoryId equals c.CategoryID 
                select new
                {
                    JobTitle = j.JobTitle,
                    JobID = j.JobID,
                    ReqSkillCommaSeperated = j.ReqSkills,
                    Category = c.Name
                    // Add other properties as needed
                }).AsEnumerable()
    .Select(x => new
    {
        JobID = x.JobID,
        JobTitle = x.JobTitle,
        Category = x.Category,
        SkillNames = GetSkillName(x.ReqSkillCommaSeperated , skills)
    }).ToList();

Assuming you have a method called GetSkillName which accepts the comma separated skillId and the skills dictionary and return a string of comma separated skill names

private string GetSkillName(string reqSkill, Dictionary<int, string> skills)
{
    if (reqSkill == null) return string.Empty;
    var skillArr = reqSkill.Split(',');
    var skillNameList = skillArr.Select(skillId => skills[Convert.ToInt32(skillId)])
                                .ToList();
    return String.Join(",", skillNameList);
}

Another option is to create a new table called JobSkills where you will store the JobId and SkillId. With this approach, you can simply write a join between the three tables and get the data as needed.

If i make a JobSkills table, how can store skills of a specific job? Should i store skill in submit? How can i do this?

Collectives™ on Stack Overflow

Get value for each of array from another table in Web Api LINQ-Asp

Edited 18-04-17

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edited 18-04-17

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related