如何使用EF6存储过程？ [英] How to use stored procedure with EF6 ?

问题描述

i创建了这个视图：

i created this view :

CREATE OR REPLACE VIEW `view_items_accounts_tree_with_date` as
							SELECT Acitems.accitem_AccID,
                            acc.accdet_Cat,
                            acc.accdet_AccountCode,
										CONCAT_WS(' - ', acc.accdet_AccountCode, acc.accdet_LangAccName) AS MergName ,
									 	AcM.accm_ID,
                                        Acitems.accitem_ID,
                                        Acitems.accitem_Debit AS Debit,
										Acitems.accitem_Credit AS Credit,
										AcM.accitem_Date
                                         
						    FROM account_items Acitems
                                        
                               LEFT JOIN (SELECT Acmains.accm_ID,
                                            Acmains.accitem_Date
								  FROM account_main Acmains
								  GROUP BY Acmains.accm_ID,Acmains.accitem_Date) AcM
								ON Acitems.accm_ID = AcM.accm_ID
                                
                                LEFT JOIN (SELECT acct.accdet_ID,
									  acct.accdet_Cat,
									  acct.accdet_AccountCode,
									  acct.accdet_LangAccName
								  FROM accounts_detials acct
								  GROUP BY acct.accdet_ID) acc
								
				          	    ON Acitems.accitem_AccID = acc.accdet_ID;

然后我创建了这个存储过程来选择积分总和，借记金额和余额两个日期之间

then i created this Stored procedure to select sum of the credits , sum of the debit and the balance between two dates

CREATE PROCEDURE `getAccountsItems_WithDate_byDates`(in startDate Datetime, in endDate Datetime)
BEGIN
		SELECT 
        accdet_Cat,
        accdet_AccountCode,
        accitem_AccID,
		MergName,
		accm_ID,
        accitem_ID,
        accitem_Date,
			FORMAT(SUM(Debit),2) Debit ,
			FORMAT(SUM(Credit),2) Credit ,
			FORMAT((SUM(Debit)-SUM(Credit)),2) Balance
        FROM view_items_accounts_tree_with_date 
        where accitem_Date between startDate And  endDate
        Group By accitem_AccID;
END

我的尝试：



i在创建导入函数后使用EF6用数据填充TreeList或Gridview但是Balance（这是VIEWS中不存在的自定义文件而在表中不存在）返回null。

What I have tried:

i used EF6 to fill a TreeList or Gridview with the data after creating an import function but Balance (this is custom filed not exist in the VIEWS and not exist in the table) return null.

treeList1.DataSource = DB1.fncgetAccountsItems_WithDate_byDates(strat, end).ToList();

推荐答案

请参考：在实体框架中使用存储过程 [ ^ ]



最重要的是，你需要设置导入选择存储过程到实体模型创建复杂类型并返回所有字段。



注意，余额字段是在借记和信用字段上计算的，因此您可以随时在运行时计算它。

Please, refer this: Use Stored Procedure in Entity Framework[^]

The most important thing is that, that you need to set "Import selected stored procedure into entity model" to create complex type and return all fields as a result.

Note, that "Balance" field is calculated on "Debit" and "Credit" fields, so you can always calculate it in run-time.

treeList1.DataSource = DB1.fncgetAccountsItems_WithDate_byDates(strat, end)
    .ToList()
    .Select(x=> new
    {
        //re-write fields here
        //and add
        Balance = x.Debit - x.Credit
    })
    .ToList();

祝你好运！

Good luck!

这篇关于如何使用EF6存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！