LINQ Error when grouping

[lithium112]

I'm needing some LINQ expert advice. I am having to convert a SQL stored proceedure over to LINQ.
The SQL statement is structured like below:

SELECT SUM((Amt)
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table1.Date < endDate
GROUP BY Table2.Name

How would I group all of the amounts and group them by the name on table2? I have tried numerous ways to do this. Below is one way that I tried to do this:

var query = from Table1 in datatable.AsEnumerable()
            join Table2 in Datatable2.AsEnumerable()
            on Table1.Field<int>("ID") equals Table2.Field<int>("ID")
            where Table1.Field<DateTime>("Date").Date < enddate.Date
            group Table2 by new {  Name = Table2.Field<string>("Name") } into c
            select new
            {
                  Amount = c.Sum(p => p.Field<double>("Amount"))
            };

I am able to successfully pull all of the names and group the names, but when it comes to the various amounts and add them together by how they are grouped, I have a really hard time trying to get it to work. The code above says that the column amount doesn't exist in Table2. Would anybody happen to know how to do this successfully? I would highly appreciate any help.

[AleMonteiro]

I'm not a LINQ expert, but analysing your code, the first thing I'd try would be:

var query = from Table1 in datatable.AsEnumerable()
            join Table2 in Datatable2.AsEnumerable()
            on Table1.Field<int>("ID") equals Table2.Field<int>("ID")
            where Table1.Field<DateTime>("Date").Date < enddate.Date
            group Table2 by new {
                    Name = Table2.Field<string>("Name"), 
                    Amount = Table2.Field<double>("Amount"),
                } into c
            select new
            {
                  Amount = c.Sum(p => p.Field<double>("Amount"))
            };

[lithium112]

Thanks for the response. Table2 doesn't have the amount. Only Table1. What I need is for the Table1 amount to group by the table2 names like in the sql query. I did try Amount = Table1.Field<double>("Amount") but it still doesn't like that. Any ideas? I'll keep researching and will post if I find an answer.

[AleMonteiro]

Could you post your table schema and the desired result?

[lithium112]

The desired result is something like this:
ID Amount
1 3625
2 8963
3 8963

The amount section is the sum of all of ID 1's amounts and so on. The ID is the easy part. I've got that. But getting the amount is giving me trouble.

[lithium112]

Finally found it!!! I had to do a little tweaking with the group by and the Amount towards the end. Here's my final result indluding the ID column:

    var query = from Table1 in datatable.AsEnumerable()
                join Table2 in Datatable2.AsEnumerable()
                on Table1.Field<int>("ID") equals Table2.Field<int>("ID")
                where Table1.Field<DateTime>("Date").Date < enddate.Date
                group new {Table1, Table2} 
                by new {Name = Table2.Field<string>("Name")} 
                into c
                select new
                {
                      Value = c.Key,
                      Amount = c.Sum(p => p.Table1.Field<double>("Amount"))
                };

I really appreciate the assistance in trying to figure this out.

[AleMonteiro]

Nice job man =)

Cheers!

Just mark as solved to finish it ^^