Entity Framework 無法更新 EntitySet 'customer_payment_type'，因為它有 DefiningQuery

當我再改公司一個Ticket時，我更新了一個資料庫的Entity Framework
更新完之後發生了一個錯誤

無法更新 EntitySet 'customer_payment_type'，因為它有 DefiningQuery，但是在 <ModificationFunctionMapping> 項目中沒有 <InsertFunction> 項目來支援目前的作業

查了一下Google是資料表沒有索引
所以如果將資料表加上索引重新更新EF就可以解決這一個問題

但是呢這一個資料表在某些原因下無法加入索引
所以該怎麼解決呢

1. 右鍵點擊edmx文件，選擇Open with，XML編輯器
2. 在edmx：StorageModels元素中找到實體
3. 完全刪除DefiningQuery
4. 重命名store:Schema="dbo"為Schema="dbo"(否則，代碼將生成一個錯誤，說名稱是無效的)

所以簡單說呢就是把edmx打開XML編輯模式

把

<EntitySet Name="customer_payment_type" EntityType="Self.customer_payment_type"
store:Type="Tables" store:Schema="dbo">
<DefiningQuery>SELECT 
    [customer_payment_type].[customer_id] AS [customer_id], 
    [customer_payment_type].[customer_payment_type_no] AS [customer_payment_type_no], 
    [customer_payment_type].[payment_type] AS [payment_type], 
    [customer_payment_type].[payment_condition] AS [payment_condition], 
    [customer_payment_type].[period_days] AS [period_days], 
    [customer_payment_type].[closed_date] AS [closed_date], 
    [customer_payment_type].[description] AS [description], 
    [customer_payment_type].[remark] AS [remark], 
    [customer_payment_type].[default_value] AS [default_value], 
    [customer_payment_type].[credit_currency] AS [credit_currency], 
    [customer_payment_type].[credit_amount] AS [credit_amount], 
    [customer_payment_type].[createdby] AS [createdby], 
    [customer_payment_type].[createdtime] AS [createdtime], 
    [customer_payment_type].[revisedby] AS [revisedby], 
    [customer_payment_type].[revisedtime] AS [revisedtime], 
    [customer_payment_type].[applyby] AS [applyby], 
    [customer_payment_type].[applytime] AS [applytime], 
    [customer_payment_type].[approvedby] AS [approvedby], 
    [customer_payment_type].[approvedtime] AS [approvedtime], 
    [customer_payment_type].[workflowstate] AS [workflowstate], 
    [customer_payment_type].[workflowstate_remark] AS [workflowstate_remark], 
    [customer_payment_type].[rowstate] AS [rowstate], 
    [customer_payment_type].[update_remark] AS [update_remark], 
    [customer_payment_type].[update_flag] AS [update_flag]
    FROM [dbo].[customer_payment_type] AS [customer_payment_type]
</DefiningQuery>
</EntitySet>

更新成

<EntitySet Name="customer_payment_type" EntityType="Self.customer_payment_type"
Schema="dbo" store:Type="Tables" /> 

收工...

遞迴父子階層

遞迴除了在面試考題很常出之外，最常應用的應該就是在撈父子階層了
假設有一個資料表如下

| Id | Name    | ParentId |
|-------------------------|
| 1  | Ricky   | 2        |
| 2  | Jackie  | 3        |
| 3  | Peter   | 4        |
| 4  | Anson   | 5        |
| 5  | Austin  | 0        |

ParentID是主管的ID，所以這張表的意思就是

Ricky 的主管是 Jackie 
Jackie 的主管是 Peter 
Peter 的主管是 Anson 
Anson 的主管是 Austin 

這時候我想撈Ricky所有的上司，就可以使用遞迴

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApp1
{
    class Program
    {
        private static List _user = new List();            
        static void Main(string[] args)
        {
            _user.Add(new User { Id = 1, Name = "Ricky", ParentID = 2 });
            _user.Add(new User { Id = 2, Name = "Jackie", ParentID = 3 });
            _user.Add(new User { Id = 3, Name = "Peter", ParentID = 4 });
            _user.Add(new User { Id = 4, Name = "Anson", ParentID = 5 });
            _user.Add(new User { Id = 5, Name = "Austin", ParentID = 0 });            
            
            factorial(_user.Where(x=>x.Name=="Ricky").FirstOrDefault());//抓取Ricky上面所有的主管
            Console.ReadLine();
        }

        //遞迴抓取向上所有主管
        private static void factorial (User args)
        {
            var result = _user.Where(x => x.Id == args.ParentID).FirstOrDefault();
            if(result != null)
            {
                Console.WriteLine(string.Format("{0}的主管是{1}", args.Name, result.Name));
                factorial(result);
            }            
        }

        public class User
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public int ParentID { get; set; }
        }
    }
}

結果如下

Outlook EML 或 MHT 取出信件內容與圖片

最近公司有一個需求，他們要從Outlook另存新檔成EML或MHT檔案
然後再把EML讀出來，再轉寄給其他使用者
把EML信件內文讀出來很容易，有很多元件可以使用
但是若是EML中含有圖片，你把內文讀取出來轉寄給其他使用者時圖片是會破圖的
以下是EML格式

From 
From: from@example.com
To: to@example.com
Subject: HTML Messages with Embedded Pic in Signature
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="alternative_boundary"

This is a message with multiple parts in MIME format.

--alternative_boundary
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 8bit

test

-- 
[Picture of a Christmas Tree]

--alternative_boundary
Content-Type: multipart/related; boundary="related_boundary"

--related_boundary
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title></title>
    </head>
    <body>
        <p>test</p>
        <p class="sig">-- <br><img src="cid:0123456789"></p>
    </body>
</html>

--related_boundary
Content-Type: image/png; name="sig.png"
Content-Disposition: inline; filename="sig.png"
Content-Location: sig.png
Content-ID: <0123456789>
Content-Transfer-Encoding: base64

R0lGODlhKAA8AIMLAAD//wAhAABKAABrAACUAAC1AADeAAD/AGsAAP8zM///AP//
///M//////+ZAMwAACH/C05FVFNDQVBFMi4wAwGgDwAh+QQJFAALACwAAAAAKAA8
AAME+3DJSWt1Nuu9Mf+g5IzK6IXopaxn6orlKy/jMc6vQRy4GySABK+HAiaIoQdg
uUSCBAKAYTBwbgyGA2AgsGqo0wMh7K0YEuj0sUxRoAfqB1vycBN21Ki8vOofBndR
c1AKgH8ETE1lBgo7O2JaU2UFAgRoDGoAXV4PD2qYagl7Vp0JDKenfwado0QCAQOQ
DIcDBgIFVgYBAlOxswR5r1VIUbCHwH8HlQWFRLYABVOWamACCkiJAAehaX0rPZ1B
oQSg3Z04AuFqB2IMd+atLwUBtpAHqKdUtbwGM1BTOgA5YhBr374ZAxhAqRVLzA53
OwTEAjhDIZYs09aBASYq+94HfAq3cRt57sWDct2EvEsTpBMVF6sYeEpDQIFDdo62
BHwZApjEhjW94RyQTWK/FPx+Ahpg09GdOzoJ/ESx0JaOQ42e2tsiEYpCEFwAGi04
8g6gSgNOovD0gBeVjCPR2BIAkgOrmSNxPo3rbhgHZiMFPnLkBg2BAuQ2XdmlwK1Z
ooZu1sRz6xWlxd4U9GIHwOmdzFgCFKCERYNoeo2BZsPp0KY+A/OAfZDYWKJZLZBo
1mQXdlojvxNYiXrD8I+2uEvTdFJQksID0XjXiUwjJm6CzBVeBQgwBop1ZPpC8RKt
YN5RCpS6XiyMht093o8KcFFf/vKE0dCmaLeWYhQMwbeQaHLRfNk9o5Q13lQGklFQ
aMLFRLcwcN5qSWmGxS2jKQQFL9nEAgxsDEiwlAHaPPJWIfroo6FVEun0VkL4UABA
CAjUiIAFM2YQogzcoLCjC3HNsYB1aSBB5JFrZBABACH5BAkUAAsALAAAAAAoADwA
AwT7cMlJa3U2670x/6DkjKQXnleJrqnJruMxvq8xHDQbJEyC5yheAnh6MI5HYkgg
YNgGSo7BcGAMBNHNYGA7ELpZiyFBLg/DFvLArEBPHoAEgXDYChQP90IAoNYJCoGB
aACFhX8HBwoGegYAdHReijZoBXxmPWRYYQ8PZmSZZHmcnqBITp2jSgIBN5BVBFwC
BVkGAQJPiVV2rFCrCq1/sXUHAgQFAL45BncFNgSfW8wASoKBB59lhoVAnQqfDNCf
AJ05At5msHPiCeSqLwUBzF6nVnXSuIwvTDYGsXPhiMmSRUOWAC436HmZU+yGDQYF
81FhV+aevzUM3oHoZBD7W7Zs9VaUIhOn4pwE38p0srLCQCqSciBFUuBFGgEryj7E
Ojhg2yOG1hQMIMCEy4p8PB8llKmAIReiW040keUvmUygiexcwbWJwxUrzBDW+Thn
qLEB5UDUe0LxYwJmAhKk+pAqVLZE69qWGZpTQwG7ZISuw7uwzDFAXTXYYoJraKym
Q/HSASDpiiUFljbYitfYRtCF635yMRBUn4UA8aYclCw0shefW7gUgPxBKGPHA5pK
MpwsKy5AcmNZSIVHjdjI2eLwVZlK44IHQT8lkq7XTDznrAIEWMTErZwbsT/hQj1L
noXLV6YwS5eIJqIDf4tyLZB5Av1ZNrLzQSplrXVkOgxItBU1E+DCwC2xFZUME5dZ
c5AB9aw2jXkSQLhFIrj4xAx9szGWzwABdkGATwuAeEokW4wY24oK8MMViAjxxcc8
E0CUAYETIKAjAifgWGMI2ehBgVtCeleGEkYmeUYGEQAAIfkECRQACwAsAAAAACgA
PAADBPtwyUlrdTbrvTH/oOSMpBeeV4muqcmu4zG+r6EcNBskSoLnJ4VQCAw9ErzE
oxgSCBSGwYDJMRgOhIGAupFGsVEG12JAmpHicaU3QDPe6fHjoSAQDlIBY6leDIUD
dnp9C04DdXh3eAaEUTeKdwJRagUCBGdnW3JHmJh8XHNmWAeLDwCfRQIBA6MMiQMG
AgBcBgGSUgeuWQMAvb1MAgWruXAMrJYAUkU2wVGXDGZeAIxMCgVfaJhOVkB/PWeX
nXM5AnScSKR2dmZzqCwFUAKjo1l4XpLULNuwWXYHAHgWCYD15AXBgV+wEACg7sDA
A45oaLFy5ZKvXvYMEPCGYvvOwQOYAHRCQufFuU7/wp2Zo2AKCgPtwN3xR8/LLpcg
kg1khaVlQyw8GRAwlC8nvp2HeM5UR8CYxp05L8ay8YcplmLGtmniwCtKLFhJR9oR
amnAuBAiH9wK9G1kAgaxBCg5u6HdSUzp1LlNCqJAgZGBaC41Q6DAUAUfajm5ZUdK
v7z08ATjmKGWAltecaVTqE5oFisB/EIpSiH06IcKpQTa3JSVagPCWm7wZsgOwJkg
3xaTrJFkFgvtFHDywmt1J2iB2pC0C9x0yItnsLx1K8xdoQDYCcQ9I5KwaynaalUS
RnpBpYH4YiXoTipgIlIFtLSUFKwSBb/NtGCnb2Zl51fHo8hnhRZbSfCEKkgZkkcw
TgBgyVdxeQNRMNNMoMBOpBxFUSx+ObgYPgS1BBRss/jxxzwAqsbLRfwh1VJyF5WI
2AkIAIAAAiiUKMGMICDRXQIn6IiCW4Qs4NYZTByppBkbRAAAIf4ZQm95J3MgSGFw
cHkgSG9saWRheXMgUGFnZQA7

--related_boundary--

--alternative_boundary--

之中的這一段就是信件的內文，使用許多元件(CDO Message)可以取出這樣的信件內容
參考這裡 https://github.com/riesvriend/CDO-EML-Parsing-Sample

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title></title>
    </head>
    <body>
        <p>test</p>
        <p class="sig">-- <br><img src="cid:0123456789"></p>
    </body>
</html>

這邊可以看出內文含有圖片，但是你把這一段內文送給StmpClient去寄送，圖片是無法顯示的
原因就是Outlook會把圖像作為MIME附件包含在電子郵件中，每個附件都必須有一個“Content-ID”標題
所以圖片只要指定CID就可以顯示出圖片，這也就是為什麼EML裡面會含有圖片的base64

所以我只取出信件內文是不夠的，我還要把EML裡面圖片的Base64一一取出
至於image Base64怎麼抓，我是直接抓取EML字串的
再使用LinkedResource加回Outlook中

string emailReadyHtml = string.empty;
emailReadyHtml += "<p>Hello World, below are two embedded images : </p>";
emailReadyHtml += "<img src=\"cid:yasser\" >";
emailReadyHtml += "<img src=\"cid:smile\" >";
MailMessage mailMessage = new MailMessage();

mailMessage.To.Add("yasser@mail.yy");
mailMessage.From = new MailAddress("info@mail.yy", "Info");

mailMessage.Subject = "Test Mail";
mailMessage.IsBodyHtml = true;

string image1Path = HttpContext.Current.Server.MapPath("~/Content/images/yasser.jpg");
byte[] image2Bytes = someArrayOfByte;

ContentType c = new ContentType("image/jpeg");

LinkedResource linkedResource1 = new LinkedResource(imagePath);
linkedResource1.ContentType = c ;
linkedResource1.ContentId = "yasser";
linkedResource1.TransferEncoding = TransferEncoding.Base64;

LinkedResource linkedResource2 = new LinkedResource(new MemoryStream(image2Bytes));
linkedResource2.ContentType = c;
linkedResource2.ContentId = "smile";
linkedResource2.TransferEncoding = TransferEncoding.Base64;

AlternateView alternativeView = 
  AlternateView.CreateAlternateViewFromString(emailReadyHtml, null, MediaTypeNames.Text.Html);

alternativeView.ContentId = "htmlView";
alternativeView.TransferEncoding = TransferEncoding.SevenBit;

alternativeView.LinkedResources.Add(linkedResource1) ;
alternativeView.LinkedResources.Add(linkedResource2);

mailMessage.AlternateViews.Add(alternativeView);

SmtpClient smtpClient = new SmtpClient();
smtpClient.Send(mailMessage);

這真的是有點麻煩，也搞了我好幾天