Linq中left join之多表查询

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using Newtonsoft.Json;

namespace CLibrary.ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            var tableAAA = "[{\"companycode\":\"80463417\",\"securitycode\":\"603978\",\"securityshortname\":null,\"financecode\":null,\"purchasedate\":\"2017-07-26T00:00:00\",\"listingdate\":\"2017-08-07T00:00:00\",\"issueprice\":29.93}]";
            var tableBBB = "[{\"securityvarietycode\":\"1000576786\",\"companycode\":\"80463417\",\"securitycode\":\"603978\",\"shares\":1000}]";
            var tableTTT = "[{\"securityvarietycode\":\"1000576786\",\"srkpj\":35.92,\"srspj\":43.1,\"srzdf\":44.0027,\"srhsl\":0.06}]";
            var tableEEE = "[{\"secucode\":\"603978\",\"tdate\":\"2017-08-07T00:00:00\",\"high\":43.1},{\"secucode\":\"603978\",\"tdate\":\"2017-08-08T00:00:00\",\"high\":47.41}]";
            var tableMMM = "[]";
            var tableJJJ = "[{\"secucode\":\"603978\",\"tdate\":\"2017-08-07T00:00:00\",\"avgprice\":42.82},{\"secucode\":\"603978\",\"tdate\":\"2017-08-08T00:00:00\",\"avgprice\":47.41}]";
            var tableNNN = "[{\"secucode\":\"603978\",\"tdate\":\"2017-08-07T00:00:00\",\"high\":43.1},{\"secucode\":\"603978\",\"tdate\":\"2017-08-08T00:00:00\",\"high\":47.41}]";

            var tableA = JsonConvert.DeserializeObject<List<TableA>>(tableAAA);
            var tableB = JsonConvert.DeserializeObject<List<TableB>>(tableBBB);
            var tableT = JsonConvert.DeserializeObject<List<TableT>>(tableTTT);
            var tableE = JsonConvert.DeserializeObject<List<TableE>>(tableEEE);
            var tableM = JsonConvert.DeserializeObject<List<TableM>>(tableMMM);
            var tableJ = JsonConvert.DeserializeObject<List<TableJ>>(tableJJJ);
            var tableN = JsonConvert.DeserializeObject<List<TableE>>(tableNNN);

            var query = from a in tableA
                        join b in tableB on a.companycode equals b.companycode into ab
                        from def_b in ab.DefaultIfEmpty(new TableB())
                        join t in tableT on def_b.securityvarietycode equals t.securityvarietycode into bt
                        join e in tableE on a.listingdate equals e.tdate into ae
                        join m in tableM on a.securitycode equals m.securitycode into am
                        from def_m in am.DefaultIfEmpty(new TableM())
                        join j in tableJ on def_m.tdatep equals j.tdate into mj
                        join n in tableN on def_m.tdatep equals n.tdate into mn
                        from def_t in bt.DefaultIfEmpty(new TableT())
                        from def_e in ae.DefaultIfEmpty(new TableE())
                        from def_j in mj.DefaultIfEmpty(new TableJ())
                        orderby def_m.tdatep
                        select new Result
                        {
                            listingopen = def_t.srkpj,
                            listingclose = def_t.srspj,
                            listingopenpremium = Math.Round((def_t.srkpj / a.issueprice - 1) * 100, 2),
                            listingchg = def_t.srzdf,
                            listingturnover = def_t.srhsl,
                            listinghighpchg = Math.Round((def_e.high / a.issueprice - 1) * 100, 2),
                            opendate = def_m.tdatep,
                            highpchg = 0d, //api层处理，
                            limitupdays = def_m.days,
                            listingavg = def_j.avgprice,
                            profit = (def_j.avgprice - a.issueprice) * def_b.shares,//api层处理，
                            issuePrice = a.issueprice,//用于api层处理
                            shares = def_b.shares,//用于api层处理 
                        };

            var list = query.ToList();

            Console.WriteLine(JsonConvert.SerializeObject(list));
            Console.ReadKey();
        }

        #region Class
        private class TableA
        {
            public string companycode { get; set; }
            public string securitycode { get; set; }
            public string securityshortname { get; set; }
            public string financecode { get; set; }
            public DateTime purchasedate { get; set; }
            public DateTime listingdate { get; set; }
            public double issueprice { get; set; }
        }
        private class TableB
        {
            public string securityvarietycode { get; set; }
            public string companycode { get; set; }
            public string securitycode { get; set; }
            public int shares { get; set; }
        }

        private class TableE
        {
            public string secucode { get; set; }
            public DateTime tdate { get; set; }
            public double high { get; set; }
        }
        private class TableJ
        {
            public string secucode { get; set; }
            public DateTime tdate { get; set; }
            public double avgprice { get; set; }
        }
        private class TableM
        {
            public string securitycode { get; set; }
            public DateTime tdatep { get; set; }
            public int days { get; set; }
        }
        private class TableT
        {
            public string securityvarietycode { get; set; }
            public double srkpj { get; set; }
            public double srspj { get; set; }
            public double srzdf { get; set; }
            public double srhsl { get; set; }
        }
        private class Result
        {
            public double listingopen { get; set; }
            public double listingclose { get; set; }
            public double listingopenpremium { get; set; }
            public double listingchg { get; set; }
            public double listingturnover { get; set; }
            public double listinghighpchg { get; set; }
            public DateTime opendate { get; set; }
            public double highpchg { get; set; }
            public int limitupdays { get; set; }
            public double listingavg { get; set; }
            public double profit { get; set; }
            public double issuePrice { get; set; }
            public int shares { get; set; }
        }

        #endregion

    }

}