LINQ to SQL and LINQ to Entity Join with static AND condition

Question

I'm trying to convert the following Join statement into LINQ TO SQL or LINQ to Entity. I know how to join tables in either implementation; but, i'm struggling with the AND clause in the Join statement.

SELECT DISTINCT
    p.LastName, 
    p.FirstName
FROM
    dbo.Patient p INNER JOIN dbo.FormPat fp ON p.PatientID = fp.PatientID
    INNER JOIN dbo.TxCyclePhase tcp ON fp.TxCyclePhase = tcp.TxCyclePhaseID AND tcp.Type = 2

As far as LINQ to SQL is concerned, I have the followings:

 var query = (from p in Context.Set<Patient>().AsNoTracking()
        join fp in Context.Set<PatientForm>().AsNoTracking() on p.Id equals fp.PatientId
        join tcp in Context.Set<TxCyclePhase>().AsNoTracking() on new { fp.TxCyclePhaseId, seconProperty = true } equals new { tcp.Id, seconProperty = tcp.Type == 2 }
        select new
        {
            p.FirstName,
            p.LastName,

        }).Distinct();

However, I'm getting an ArgumentNullException on the second join statement.

For the LINQ to Entity, I have the followings, however, this is giving me a distinct IQueryable of FormPat, instead of Patient.

var patients = Context.Set<Patient>().AsNoTracking()
                .SelectMany(p => p.Forms)
                .Where(fp => fp.Phase.Type == 2)
                .Distinct();

I think my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) will help you. In this case, rule 6: create an anonymous object on each side of the join `equals`. — NetMage, Sep 06 '19 at 17:10

score 0 · Answer 1 · answered Sep 06 '19 at 15:07

As far as the LINQ to Entity is concerned, I was able to figure it out. I'd still like to know how to do it in LINQ to SQL tho.

I'm using the EF fluent API. My Patient object looks like:

public Patient()
        {
            Programs = new HashSet<Program>();
        }
        public virtual ICollection<PatientForm> Forms { get; set; }

My PatientForm object looks like:

public class PatientForm
    {
        public int FormId { get; set; }
        public Patient CurrentPatient { get; set; }
        public TxCyclePhase Phase { get; set; }
    }

And the CyclePhase object looks like:

public TxCyclePhase()
        {
            this.FormPats = new HashSet<PatientForm>();
        }

        public int Id { get; set; }
        public virtual ICollection<PatientForm> FormPats { get; set; }

In the entity configurations, I have the relationships set. So, in the repository, all I have to do is to use the Any() function when selecting the Patient forms.

var patients = Context.Set<Patient>().AsNoTracking()
                .Where(p => p.Forms.Any(f => f.Phase.Type == 2))
                .Distinct();

LINQ to SQL and LINQ to Entity Join with static AND condition

1 Answers1