Użycie CompiledQuery w Entity Framework

private static IList<byte> OpenStatuses = new List<byte>(new byte[] { (byte)Open, (byte)InProgress });

private static readonly Func<db, byte, long, IQueryable<RequestEF>> GetQuery =
            CompiledQuery.Compile<db, byte, long, IQueryable<RequestEF>>(
                (ctx, salesId, assignedId) =>
                    from csr in ctx.CSR
                    join sales in ctx.SALES
                         on csr.SalesId equals salesOrg.SalesId
                    join customer in ctx.CUSTOMER
                         on csr.CustomerId equals customer.CustomerId
                    join user in ctx.USER
                         on csr.CreatedByUserId equals user.UserId
                    join ct in ctx.CALL
                         on csr.CallId equals ct.Id into joinedCsrCall
                    from callType in joinedCsrCall.DefaultIfEmpty()
                    join uAg in ctx.GROUP
                         on csr.AssignedId equals uAg.Id into joinedEmpDept
                    from group in joinedEmpDept.DefaultIfEmpty()
                    where csr.SalesId == salesId && csr.AssignedId == assignedId &&
                      OpenStatuses.Contains(csr.StatusId)
                    select new RequestEF
                    {
                        Group = group,
                        CSR = csr,
                        Sales = sales,
                        Customer = customer,
                        User = user,
                        Call = call
                    });

I wywołanie skompilowanego zapytania:

GetQuery.Invoke(db, salesId, assignedId)

Czytanie excela przez OleDB

public class ExcelReaderHelper
    {
        /// <summary>
        /// List of Worksheet names
        /// </summary>
        public List<string> WorkSheetNames { get; private set; }  

        private string connectionString;

        /// <summary>
        /// Read the excel file using OLEDB
        /// </summary>
        /// <param name="fileName">Path and file name of the Excel Workbook</param>
        public ExcelReaderHelper(string fileName)
        {            
            WorkSheetNames = new List<string>();

            OpenExcelFile(fileName);
        }

        public DataTable GetWorksheet(string worksheetName)
        {
            DataSet excelDataSet = new DataSet();

            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [" + worksheetName + "]", con);

                con.Open();
                cmd.Fill(excelDataSet);
            }

            return excelDataSet.Tables[0];
        }

        public DataTable GetWorksheet()
        {
            string firstWorksheetName = WorkSheetNames.FirstOrDefault();

            if (string.IsNullOrEmpty(firstWorksheetName)) return new DataTable();

            return GetWorksheet(firstWorksheetName);
        }

        private void OpenExcelFile(string fileName)
        {            
            //read a 97-2003 file
            connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"", fileName);

            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();

                //get all the available sheets
                DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                foreach (DataRow row in dataSet.Rows)
                {
                    WorkSheetNames.Add(row["TABLE_NAME"].ToString());
                }
            }
        }
    }

IMEX=1 – czyta wszystkie dane jako string (Uwaga! Należy pamiętać, że formatowanie kwot (np. cen) będzie zgodne z ustawieniami regionalnymi (culturą) servera/usługi, na którym/która to czytanie wykonuje.