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
– cdhowie, Commented Aug 18, 2011 at 4: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
– Darf, Commented Aug 18, 2011 at 4:45

Enigmativity · Accepted Answer · 2011-08-18 04:51:34Z

3

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

answered Aug 18, 2011 at 4:51

Enigmativity

117k12 gold badges101 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darf Over a year ago

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

Kirk Broadhurst · Accepted Answer · 2011-08-18 05:12:17Z

0

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.

answered Aug 18, 2011 at 5:12

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

1 Comment

Darf Over a year ago

I'll test that tomorrow. It's midnight

Collectives™ on Stack Overflow

Convert this SQL Query to LINQ Query

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related