none
show record in entity framwork or linq RRS feed

  • Question

  • i have record

    voucher_id    voucher_Date    Voucher_type     Total

    1                        1/2/2015      sale                     10000

    2                        1/2/2015      sale                     15000

    3                        1/2/2015      purchase             5000

    4                        25/2/2015      sale                   10000

    how to display data in date wise as,

    Voucher_Date     Sale         Purchase

    1/2/2015           25000        5000

    25/2/2015         10000

    • Moved by Kristin Xie Wednesday, March 4, 2015 9:23 AM move to better forum
    Tuesday, March 3, 2015 10:56 AM

Answers

  • Try a query like this:

    var q =

           VoucherTable

                  .GroupBy( v => v.voucher_Date )

                  .Select( g => new

                  {

                         Voucher_Date = g.Key,

                         Sale = g.Where( v => v.Voucher_type == "sale" ).Sum( v => v.Total ),

                         Purchase = g.Where( v => v.Voucher_type == "purchase" ).Sum( v => v.Total )

                  } );

    Adjust the name ‘VoucherTable’ according to your EF names. If required, also order by date.


    Tuesday, March 3, 2015 1:00 PM

All replies

  • Hi Umesh,

    please, show me the source code 


    Mark as answer or vote as helpful if you find it useful | Ammar Zaied [MCP]

    Tuesday, March 3, 2015 11:05 AM
  • If you are getting the record from a database use in the SQL an 'Order By' clause. Once you get the data into VS display it in a DataGridView. Here is some sample code.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace WindowsFormsApplication6
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
    
                string SQL = "Select * From MyTable Order By voucher_Date";
                string connStr = "Enter Your Connection String Here";
                SqlDataAdapter adapter = new SqlDataAdapter(SQL, connStr);
    
                DataTable table = new DataTable();
                adapter.Fill(table);
                dataGridView1.DataSource = dt;
    
            }
        }
    }
    


    jdweng

    • Marked as answer by Kristin Xie Wednesday, March 4, 2015 9:11 AM
    • Unmarked as answer by Kristin Xie Wednesday, March 4, 2015 9:11 AM
    Tuesday, March 3, 2015 11:08 AM
  •   var v = from t in entity.VoucherMasterTbls
                     join t2 in entity.CompanyTbls
                     on t.ComapanyId equals t2.Comp_Id
     
                     where t.Voucher_Date.Value.Year == year && t.Voucher_Date.Value.Month == month && t.Voucher_Type == "sales" || t.Voucher_Type == "purchase"
                     select new
                     {
                         total = t.Grand_Total == null ? 0 : t.Grand_Total.Value,
                         voucher_type = t.Voucher_Type,
                         Voucher_Date = t.Voucher_Date == null ? DateTime.Now : t.Voucher_Date.Value,
                     };
    Tuesday, March 3, 2015 11:10 AM
  • Thanks sir, but how to calculate and display sale & purchase entry of same date
    Tuesday, March 3, 2015 11:25 AM
  • Try a query like this:

    var q =

           VoucherTable

                  .GroupBy( v => v.voucher_Date )

                  .Select( g => new

                  {

                         Voucher_Date = g.Key,

                         Sale = g.Where( v => v.Voucher_type == "sale" ).Sum( v => v.Total ),

                         Purchase = g.Where( v => v.Voucher_type == "purchase" ).Sum( v => v.Total )

                  } );

    Adjust the name ‘VoucherTable’ according to your EF names. If required, also order by date.


    Tuesday, March 3, 2015 1:00 PM
  • I usually use a dictionary something like the code below.  You can easily convert to Linq.

                List<List<object>> v = new List<List<object>>();
                Dictionary<DateTime, List<object>> dict = v.AsEnumerable()
                    .GroupBy(x => x["Voucher_Date"], y => y)
                    .ToDictionary(x => x.Key, y => y.ToList());
    
                foreach(DateTime key in dict.Keys)
                {
                    List<object> sameDates = dict[key];
                    double total = sameDates.Select(x => x.sameDates["Total"]).ToList().Sum();
                }


    jdweng

    Tuesday, March 3, 2015 1:08 PM
  • Hi,

    i think, that the best way is to modify your SQL command, to the needed retrieve, because in that case sql server will return much less information, and you don't need to make any actions to transform it, so your program will be more faster.

    Your sql:

      select voucher_Date, sum(SummBuy) Buy, sum(SummSell) Sell from (
      Select Buy.voucher_Date, Buy.Total SummBuy, 0 SummSell
      From tempdb.dbo.MyTable Buy
      where Buy.Voucher_type = 'sale'
      union all
      Select Sell.voucher_Date, 0 SummBuy, Sell.Total SummSell
      From tempdb.dbo.MyTable Sell
      where Sell.Voucher_type = 'purchase'
    )aa
    group by aa.voucher_Date
    order by aa.voucher_Date


    • Edited by xjomanx Tuesday, March 3, 2015 2:08 PM
    Tuesday, March 3, 2015 1:59 PM
  • You can go to Entity SQL, which is much like T-SQL.

    https://msdn.microsoft.com/en-us/library/vstudio/bb387145(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/vstudio/bb738684%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/vstudio/bb387145(v=vs.100).aspx

    Anything you can do with T-SQL can be donw with Entity SQL when it comes to querying the conceptual model, and youi can populate objects off of the model with the E_SQL Datareader and return objects off the model in a List<T>, as an example.

    Tuesday, March 3, 2015 3:29 PM