CODEWITHSUNDEEP
c#linqlinq-to-sqlentity-frameworkentity-framework-4
Shakeeb Ahmed
Shakeeb Ahmed

Reputation: 1808

SQL Query to LINQ

I want to convert this query to LINQ to use with Entity Framework 4.

Select *, 
(select SUM(Of_Orders.Contribution) from Of_Orders
where Of_Orders.CauseID = Of_Causes.CauseID) AS Earned
FROM Of_Causes

Can anyone help me to do this.

Thanks in Advance

Upvotes: 0

Views: 235

Answers (2)

RichardW1001
RichardW1001

Reputation: 1985

It cools like the CauseID field represents a foreign key? If it does then you can map an association in your entity model, so that cause.Orders is a property you can access. Side note, you can also rename the entities/properties, they don't have to match table names so you could get rid of the ugly underscores! That would then look like this:

var result = from c in context.Of_Causes
select new {cause, cause.Orders.Sum(o => o.Contribution}

You'd get a few benefits from doing that (if you can), like a cleaner model, and more efficient SQL queries being generated.

If you can't use a foreign key, I'd join the tables manually which again should help performance.

I believe that would look like this but my translation from VB to C# may be dodgy, here's the C#:

var context = new ModelContainer();
var result = from c in context.Of_Cause
             join o in context.Of_Orders on c.Id equals o.CauseID into g
             select new {Cause = c, Total = g.Sum(o => o.Contribution)};

And here's the VB original in case my translation is off!

Dim result = From cause As Of_Cause In context.Of_Cause
             Group Join order As Of_Orders In context.Of_Orders On order.CauseID Equals cause.Id Into total = Sum(order.Contribution)
             Select New With {.Cause = cause, .Total = total}

Upvotes: 1

driis
driis

Reputation: 164281

That would be something similar to:

from cause in context.Of_Causes
select new {
    cause,
    Earned = context.Of_Orders.Where(o => o.CauseID == cause.CauseID).Sum(o => o.Contribution)
};

I've made some assumptions about the types involved, but that should be easy for you to work out.

Upvotes: 1

Related Questions