CODEWITHSUNDEEP
databaselinqlinq-to-sql
Darf
Darf

Reputation: 2585

Convert this SQL Query to LINQ Query

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?

Upvotes: 2

Views: 162

Answers (2)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28738

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.

Upvotes: 0

Enigmativity
Enigmativity

Reputation: 117175

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"?

Upvotes: 3

Related Questions