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

Question

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

Answers 1
Added at 2016-12-31 19:12
Tags
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.

Answers to

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

nr: #1 dodano: 2016-12-31 19:12

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.

Source Show
◀ Wstecz