CODEWITHSUNDEEP
c#asp.net-mvclinqentity-framework-5
DeadAlive
DeadAlive

Reputation: 58

Getting sum from table and showing into razor view

I have two tables like this Table 1 : animal

Country  Lion Tiger State
india     4      5   Madhya Pradesh
india     10     2   Utrakhand
Russia    0      10  Primorsky krai
Russia    1      20  Khabarovsk Krai

and Table 2: Project_Tiger

Country  No
India    10
Russia   5

I have created inumerable class like this

public animal animal {get;set;};
public project_tiger project_tiger {get;set;};

now I want to show result something like this in view

Country  NO  lion   tiger
india    10   14     7
Russia   5    1      30

here lion and tiger is sum of both the record in table 1 10+4= 15, 5+2 =7, for india and same for russia

now I am lacking of knowledge how to query these data as sum from database using linq and how to show this in razor view

I can write query in sql like this but can't translate it to linq

select animal.country, No,  sum(lion), sum(tiger) from animal
       inner join project_tiger ON animal.country equals project_tiger.country

Any help regarding this will be appreciated.

Upvotes: 0

Views: 508

Answers (2)

Orel Eraki
Orel Eraki

Reputation: 12196

You can still acheive it using EntityFramework, and still use the power of the SQL server to preform the lifting for you.

By using directly with the generic method SqlQuery<> this can be done pretty easily.

Create a class that will fit your need

public class AnimalsCount
{
    public int No { get; set; }
    public int Lion { get; set; }
    public int Tiger { get; set; }
}

Now use the Generic method of SqlQuery<AnimalsCount>

var animalsCount = ctx.Database
                    .SqlQuery<AnimalsCount>("SELECT Country,(SELECT [No] FROM ProjectTiger WHERE Country = a.Country) as [No], sum(lion) as Lion, sum(tiger) as Tiger FROM [Animal] as a GROUP BY Country")
                    .ToList();

Upvotes: 0

Shyju
Shyju

Reputation: 218722

You basically need to join both the tables and group the results by the country name and generate the result out of that.

var groupd = (from a in dbContext.Animals
              join b in dbContext.ProjectTigers on a.Country equals b.Country
              select new { Country = a.Country, 
                           No = b.No,
                           Lion = a.Lion, 
                           Tiger = a.Tiger }
            ) // We have the join results. Let's group by now
            .GroupBy(f => f.Country, d => d,
            (key, val) => new { Country = key, 
                                No = val.First().No,
                                Lion = val.Sum(s => s.Lion), 
                                Tiger = val.Sum(g => g.Tiger) });

This will give you a collection of anonymous objects. If you have a view model/dto to represent your expected data items, you may use that in the projection part of this linq expression.

Also, like others mentioned in the comments, you might want to look into a better way of building your db schema.

Upvotes: 2

Related Questions