So here is my problem laid out as simple as I can put it.
I cant combine multiple entities together that have related data but are different
(one database table has id number that is shared by database table 2 and table 2 has employee number which is shared with database table 3, I need to limit the active employees in table 3 while searching for the current employees with id number “Something” because table 1 has their job details, which are also marked as active or none active and need to be limited in that search as well)

Table 1

Id_num          Job_desc            Job_active
123123          supervisor         checked
123123          advisor           unchecked

Table 2

Id_num       Job_id       Employee_number
123123       B04           29928
234234       B00           29999

Table 3

Employee_number        Employee_active        Employee_name
29928                               true             Alfred
29999                               false            Batman

My dilemma is that I need to combine all these table into one searchable query either using LINQ entities or just plain sql (I do not care which at this point) and then return the results as a list and print them on a view inside my app.

This is what I have so far and I know its very wrong because I have no idea what to do or what direction is better, I read that raw sql is better but then I read that its better to do what I have done below. Any guidance is appreciated, and please using coding examples in responses so I can better understand any points you might make.

(here is the sql code that i have been presented with to try to convert over to my application)

 SELECT dbo.HR_JOBLOOK.JOB_NO, dbo.HR_JOBLOOK.JOB_DESC, dbo.AREA_DEPT_REFERENCE.DEPT, dbo.HR_JOBLOOK.ACTIVE_FLAG
FROM     dbo.vw_Current_User_With_Area INNER JOIN 
    dbo.AREA_DEPT_REFERENCE ON dbo.vw_Current_User_With_Area.DEPT = dbo.AREA_DEPT_REFERENCE.DEPT INNER JOIN
            dbo.HR_JOBLOOK ON dbo.AREA_DEPT_REFERENCE.AREA = dbo.HR_JOBLOOK.DEPT
WHERE  (dbo.HR_JOBLOOK.ACTIVE_FLAG = N'Checked')

(here is what i have, and its very very wrong and i know this)

 var query = from jl in db.HR_JOBLOOK
                        join qls in db.AREA_DEPT_REFERENCE on jl.DEPT equals qls.DEPT
                        where jl.ACTIVE_FLAG == "Checked"
                        select new job_qualification_mgt { job_list = jl, quals = qls.quals };

thanks for any and all help!

Does the "Job_active" value actually equate to a string that is "checked" or "unchecked"?
Is the string check in your SQL code always case >>insensitive<<?

"checked" does not equal "Checked"

no the string values in my code are case sensitive, i just forgot to make the example tables above case sensitive.

the example should have a Checked value and not checked

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.