locked
How to display information from Linq query? RRS feed

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

    Tuesday, August 18, 2020 8:10 AM

Answers

  • User-939850651 posted

    Hi Decompressor,

    There is a clear statement in the error message that the anonymous type does not match the defined Main class.

    For example, the Main class does not contain a property named "dref_07_01". You could try to redefine a new Model, like this:

    public class TestModel
        {
            public string m07_01 { get; set; }
            public int m07_02 { get; set; }
            public int m_07_03 { get; set; }
            public string dref_07_01 { get; set; }
            public int dref_07_02 { get; set; }
            public int dref_07_03 { get; set; }
            public int total { get; set; }
            public int result { get; set; }
        }
    public IList<TestModel> IndexMain { 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) }); foreach (var item in st) IndexMain.Add(new TestModel { m07_01=item.m07_01, m07_02=item.m07_02, m_07_03=item.m_07_03, dref_07_01=item.dref_07_01, dref_07_02=item.dref_07_02, dref_07_03=item.dref_07_03, result=item.result, total=item.total }); }

    For more details about anonymous types, please refer to this document.

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 19, 2020 10:22 AM