How to display information from Linq query?

Question

User-180482348 posted

Hi, I have 3 tables 

CREATE TABLE [dbo].[main] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [calendar] DATETIME2 (7)  NOT NULL,
    [m07_01]   NVARCHAR (MAX) NULL,
    [m07_02]   INT            NOT NULL,
    [m_07_03]  INT            NOT NULL,
    CONSTRAINT [PK_main] PRIMARY KEY CLUSTERED ([Id] ASC)
);
insert main (calendar,m07_01,m07_02,m_07_03) values('28.06.2020 0:00:00','bv500000',2020,100),
('02.07.2020 0:00:00','bv100000',2020,200),('01.07.2020 0:00:00',2020,220450),
('05.07.2020 0:00:00','bv100020',2020,123948)

CREATE TABLE [dbo].[goods] (
    [Id]     INT            IDENTITY (1, 1) NOT NULL,
    [MainId] INT            NOT NULL,
    [Name]   NVARCHAR (MAX) NULL,
    [weight] INT            NOT NULL,
    CONSTRAINT [PK_goods] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_goods_main_MainId] FOREIGN KEY ([MainId]) REFERENCES [dbo].[main] ([Id]) ON DELETE CASCADE
);
insert goods (MainId,Name,weight)values(3,'balls',23400),(2,'brushes',5000),(2,'boxes',18000),(1,'balls',23400),
(4,'boxes',18000)

CREATE TABLE [dbo].[dref] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [MainId]     INT            NOT NULL,
    [dref_07_01] NVARCHAR (MAX) NULL,
    [dref_07_02] INT            NOT NULL,
    [dref_07_03] INT            NOT NULL,
    CONSTRAINT [PK_dref] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dref_main_MainId] FOREIGN KEY ([MainId]) REFERENCES [dbo].[main] ([Id]) ON DELETE CASCADE
insert dref(MainId,dref_07_01,dref_07_02,dref_07_03)values(4,'bv100000',2020,200),(3,'bv500000',2020,100)

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace Declaration.Models
{
    public class Main
    {
        [Key]
        public int Id { get; set; }
        public DateTime calendar { get; set;}
        public string m07_01 { get; set; }
        public int m07_02 { get; set; }
        public int m_07_03 { get; set; }
        public Dref Dref { get; set;}
        [NotMapped]
        public decimal total { get; set; }
        [NotMapped]
        public int result { get; set; }
        [ForeignKey("MainId")]
        public ICollection<Goods> Goods { get; set;}
    }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Declaration.Models
{
    public class Goods
    {
        public int Id { get; set; }
        public int MainId { get; set;}
        public string Name { get; set;}
        public int weight { get; set;}
        public Main Main { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Declaration.Models
{
    public class Dref
    {
        public int Id { get; set;}
        public int MainId { get; set;}
        public string dref_07_01 { get; set;}
        public int dref_07_02 { get; set;}
        public int dref_07_03 { get; set;}
        public Main Main { get; set; }

    }
}

using Declaration.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Declaration.Data
{
    public class MainContext : DbContext
    {
        public MainContext(DbContextOptions<MainContext> options)
           : base(options)
        {
        }

        public DbSet<Goods> goods { get; set; }
        public DbSet<Main> main { get; set; }
        
        public DbSet<Dref> dref { get; set; }



        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Goods>().ToTable("goods");
            modelBuilder.Entity<Main>().ToTable("main")
                .HasOne(b => b.Dref)
                .WithOne(y => y.Main)
                .HasForeignKey<Dref>(x => x.MainId);
            modelBuilder.Entity<Dref>().ToTable("dref");


        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Declaration.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace Declaration.Pages
{
    public class IndexModel : PageModel
    {
        private readonly Declaration.Data.MainContext _context;

        public IndexModel(Declaration.Data.MainContext context)
        {
            _context = context;
        }
        public IList<Main> Main { get; set; }
        //public Main Main { get; set; }
        public async Task OnGetAsync()
        {

            var query = from a in _context.main
                        join b in _context.dref on a.Id equals b.MainId
                        join c in _context.goods on a.Id equals c.MainId
                        let zet = _context.main.Where(x => x.m07_01 == b.dref_07_01 &&
                          x.m07_02 == b.dref_07_02 && x.m_07_03 == b.dref_07_03)
                        from g in zet
                        join h in _context.goods on g.Id equals h.Id
                        select new { a.m07_01, a.m07_02, a.m_07_03, b.dref_07_01,
                            b.dref_07_02, b.dref_07_03, mass = c.weight, h.weight };
            var st = (from a in query
                     select new  { a.m07_01, a.m07_02, a.m_07_03, a.dref_07_01, a.dref_07_02,
                         a.dref_07_03, total=query.Sum(t=>t.mass), 
                         result=query.Sum(y=>y.weight) });
            
            /*var st = _context.main
                .Include(y => y.Dref)
                .Include(x => x.Goods);*/
           
                                      Main =await st.ToListAsync();
           

        }
    }
}

@page
@model Declaration.Pages.IndexModel
@{
    ViewData["Title"] = "Index";
}


<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Main[0].calendar)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Main[0].m07_01)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Main[0].m07_02)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Main[0].m_07_03)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Main[0].Dref.dref_07_01)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Main[0].Dref.dref_07_02)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Main[0].Dref.dref_07_03)
            </th>
            <th>
               @Html.DisplayNameFor(model=>model.Main[0].total)
            </th>
            <th>
                @Html.DisplayNameFor(model=>model.Main[0].result)
            </th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.Main)
        {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.calendar)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.m07_01)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.m07_02)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.m_07_03)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Dref.dref_07_01)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Dref.dref_07_02)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Dref.dref_07_03)
            </td>
           
            <td>@Html.DisplayFor(modelItem=>item.total)</td>
            <td>@Html.DisplayFor(modelItem=>item.result)</td>

        </tr>
        }
    </tbody>
</table>

I need to compare sum weights of bills and display them, but when I build my query, I get error: "cannot implicitly convert type "System.Collections.Generic.List <<anonymous type:string m07_01,int m07_02...>> to 'System.Collections.Generic.List <Declaration.Models.Main>'.

An explicit conversion exists... How to correct it?