Convert this SQL Query to LINQ Query

Question

I need to convert this SQL Query to LINQ Query, also I need to expose the SQL Select properties:

SELECT Problem.ProblemID, ProblemFactory.ObjectiveID, Objective.Name, ProblemFactory.Time, ProblemType.ProblemTypeName, ProblemFactory.OperationID, 
                     ProblemFactory.Range1ID, ProblemFactory.Range2ID, ProblemFactory.Range3ID, ProblemFactory.Range4ID, 
                     ProblemFactory.MissingNumber
FROM Problem INNER JOIN ProblemFactory ON Problem.ProblemFactoryID = ProblemFactory.ProblemFactoryID
             INNER JOIN ProblemType ON ProblemFactory.ProblemTypeID = ProblemType.ProblemTypeID
             INNER JOIN Objective ON Objective.ObjectiveID = ProblemFactory.ObjectiveID

UPDATE 1:

This is what I have:

        var query = from problem in dc.Problem2s
                    from factory
                    in dc.ProblemFactories
                         .Where(v => v.ProblemFactoryID == problem.ProblemFactoryID)
                         .DefaultIfEmpty()
                    from ...

And I'm using this example: What is the syntax for an inner join in LINQ to SQL?

Ok, so what's your question? We're not going to do your job for you... — cdhowie, Aug 18 '11 at 04:41
I know, although this is just a doubt. I have not worked so well with LINQ. I need a little a help. The problem is I'm using three inner join and I don't know how to convert it in LINQ — Darf, Aug 18 '11 at 04:45

score 3 · Accepted Answer · answered Aug 18 '11 at 04:51

Something like this?

var query =
    from p in ctx.Problem
    join pf in ctx.ProblemFactory on p.ProblemFactoryID equals pf.ProblemFactoryID
    join pt in ctx.ProblemType on pf.ProblemTypeID equals pt.ProblemTypeID
    join o in ctx.Objective on pf.ObjectiveID equals o.ObjectiveID
    select new
    {
        p.ProblemID,
        pf.ObjectiveID,
        o.Name,
        pf.Time,
        pt.ProblemTypeName,
        pf.OperationID, 
        pf.Range1ID,
        pf.Range2ID,
        pf.Range3ID,
        pf.Range4ID, 
        pf.MissingNumber,
    };

But what do you mean by the "SQL Select properties"?

I think that what I was looking for. I was using a stored procedore in my database, but I realized that I cannot get the properties because I'm using several tables. So what you have done in select new you're exposing the properties and that's what I need — Darf, Aug 18 '11 at 04:54

score 0 · Answer 2 · answered Aug 18 '11 at 05:12

One of the benefits of an ORM like Linq-to-SQL is that we don't have to flatten our data to retrieve it from the database. If you map your objects in the designer (i.e. if you have their relationships mapped), you should be able to retrieve just the Problems and then get their associated properties as required...

var problems = from problem in dc.Problem2s select problem;

foreach (var problem in problems)
{
    // you can work with the problem, its objective, and its problem type.
    problem.DoThings();
    var objective = problem.Objective;
    var type = problem.ProblemType;    
}

Thus you retain a logical data structure in your data layer, rather than anonymous types that can't easily be passed around.

I'll test that tomorrow. It's midnight – Darf Aug 18 '11 at 05:15 — Darf, Aug 18 '11 at 05:15

Convert this SQL Query to LINQ Query

2 Answers2