CODEWITHSUNDEEP
entity-frameworklinqlinq-to-entities
user568551
user568551

Reputation: 449

Can't figure out a Linq join

In LinqPad, I have this working, and the underlying SQL query is what I want:

from userProcesses in UserProcesses
join formProcesses in FormProcesses 
   on userProcesses.ProcessID equals formProcesses.Process_ProcessID
join forms in Forms 
    on formProcesses.Form_FormID equals forms.FormID
join userForms in UserForms
    on new {userProcesses.UserProcessID, forms.FormID} equals 
       new {userForms.UserProcessID, userForms.FormID} into combined
from c in combined.DefaultIfEmpty()
select new
{
    UserFormID = c == null ? (int?) null : c.UserFormID,
    FormID = forms.FormID,
    Name = forms.Name
}

The problem is, in my actual code, the FormProcesses table is not exposed itself, but is a navigation property. I've been struggling with how to write this query in my software.

Here is a skeleton view of my models so you can see the navigation properties:

public class UserProcess : BaseModel
    {
        [Key]
        public int UserProcessID { get; set; }

        [Required]
        [ForeignKey("Process")]
        public int ProcessID { get; set; }

        public Process Process { get; set; }

        public virtual ICollection<UserForm> UserForms { get; set; }
    }

 public class Form : Common.BaseModel
    {
        [Key]
        public int FormID { get; set; }       

        public virtual ICollection<Process> Processes { get; set; }
    }

public class UserForm : Common.BaseModel
    {
        [Key]
        public int UserFormID { get; set; }

        [Required]
        [ForeignKey("UserProcess")]
        public int UserProcessID { get; set; }

        public UserProcess UserProcess { get; set; }

        [Required]
        [ForeignKey("Form")]
        public int FormID { get; set; }

        public Form Form { get; set; }
    }

public class Process : Common.BaseModel
    {
        [Key]
        public int ProcessID { get; set; }

        public virtual ICollection<Form> Forms { get; set; }
    }

What am I doing wrong - how can I get this working?

Upvotes: 0

Views: 39

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

You can replace all explicit joins with "navigating" through navigation properties.

For instance, the following joins

join formProcesses in FormProcesses
    on userProcesses.ProcessID equals formProcesses.Process_ProcessID
join forms in Forms
    on formProcesses.Form_FormID equals forms.FormID

become:

from forms in userProcesses.Process.Forms

which should solve the issue in question.

Optionally, LINQ to Entities way of writing the following join:

join userForms in UserForms
    on new { userProcesses.UserProcessID, forms.FormID } equals
       new { userForms.UserProcessID, userForms.FormID } into combined

is:

let combined = from userForms in userProcesses.UserForms
               where userForms.FormID == forms.FormID
               select userForms

Upvotes: 1

Related Questions