//both GetCategorizedCalls and GetCategorizedCall2 methoda work. //GetCategorizedCalls2 uses the predicate builder along with asExpandable() //GetCategorizedCalls uses the Where extensions in DynamicLibrary.cs in the using System.Data.Entity namespace public static Task<DataCalls> GetCategorizedCalls(this IRepositoryAsync<Log> _LogRepository, LogCategory LogCategory, string SelectedContestName, string SelectedCall, Logqso.mvc.common.Enum.CallGroupEnum CallGroup, bool Disabled, string Username) { //The passed in _LogRepository has a DataContext from Dependency resolver //The Unit of work DataContext does not match the DataContext of the _Logrespository //SOLUTION: use the _LogRepository to get a LogRespositpry with the UoW DataContext //This allows the join below to have all of the expression trees point to the same DBContext. //This hook fixes a problem that should not occur, var LogRepository = _LogRepository.GetRepository<Log>(); DataCalls DataCalls = new DataCalls(); string CallChar = SelectedCall.Substring(0, 1); if ( (int)CallChar[0] >= 48 && (int)CallChar[0] <= 57) { CallChar = "1"; } var LogCategoryRepository = LogRepository.GetRepository<LogCategory>(); IQueryFluent<LogCategory> LogCategorys = LogCategoryRepository.Query(); var LogCategoryQ = LogCategorys.SelectQueryable(false); if (Disabled == false) {// business rules if (LogCategory.CatOperatorEnum == (int)CatOperatorEnum.SINGLE_OP) { LogCategory.CatNoOfTxEnum = (int)CatNoOfTxEnum.ALL; } else if (LogCategory.CatOperatorEnum == (int)CatOperatorEnum.MULTI_OP) { LogCategory.CatBandEnum = (int)CatBandEnum.ALL; if (LogCategory.CatNoOfTxEnum != (int)CatNoOfTxEnum.ONE) { LogCategory.CatPowerEnum = (int)CatPowerEnum.ALL; } LogCategory.CatAssistedEnum = (int)CatAssistedEnum.ALL; } else if (LogCategory.CatOperatorEnum == (int)CatOperatorEnum.CHECKLOG) { LogCategory.CatBandEnum = (int)CatBandEnum.ALL; LogCategory.CatPowerEnum = (int)CatPowerEnum.ALL; LogCategory.CatAssistedEnum = (int)CatAssistedEnum.ALL; LogCategory.CatNoOfTxEnum = (int)CatNoOfTxEnum.ALL; } else { //all } } else {//all disabled LogCategory.CatOperatorEnum = (int)CatOperatorEnum.ALL; LogCategory.CatBandEnum = (int)CatBandEnum.ALL; LogCategory.CatPowerEnum = (int)CatPowerEnum.ALL; LogCategory.CatAssistedEnum = (int)CatAssistedEnum.ALL; LogCategory.CatNoOfTxEnum = (int)CatNoOfTxEnum.ALL; } //Using Predicate var Predicate = PredicateBuilder.True<LogCategory>(); if (LogCategory.CatAssistedEnum != (int)CatAssistedEnum.ALL) { Predicate = Predicate.And(p => p.CatAssistedEnum == LogCategory.CatAssistedEnum); } if (LogCategory.CatBandEnum != (int)CatBandEnum.ALL) { Predicate = Predicate.And(p => p.CatBandEnum == LogCategory.CatBandEnum); } if (LogCategory.CatNoOfTxEnum != (int)CatNoOfTxEnum.ALL) { Predicate = Predicate.And(p => p.CatNoOfTxEnum == LogCategory.CatNoOfTxEnum); } if (LogCategory.CatOperatorEnum != (int)CatOperatorEnum.ALL) { Predicate = Predicate.And(p => p.CatOperatorEnum == LogCategory.CatOperatorEnum); } if (LogCategory.CatOperatorOverlayEnum != null && LogCategory.CatOperatorOverlayEnum != (int)CatOperatorOverlayEnum.NONE) { Predicate = Predicate.And(p => p.CatOperatorOverlayEnum == LogCategory.CatOperatorOverlayEnum); } if (LogCategory.CatPowerEnum != (int)CatPowerEnum.ALL) { Predicate = Predicate.And(p => p.CatPowerEnum == LogCategory.CatPowerEnum); } var ContestRepository = LogRepository.GetRepository<Contest>(); IQueryFluent<Contest> Contests = ContestRepository.Query(); var ContestQ = Contests. SelectQueryable(false).Where(x => x.ContestName == SelectedContestName).FirstOrDefault(); var CallSignRepository = LogRepository.GetRepository<CallSign>(); IQueryFluent<CallSign> CallSigns = CallSignRepository.Query(); IQueryFluent<Log> Logs = LogRepository.Query(); var LogQ1 = Logs .SelectQueryable(false).Where(x => x.ContestId == ContestQ.ContestId); CallSignRepository.SetSQLLogging(true); Predicate = Predicate.And(p => LogQ1.Any(o => o.LogCategoryId == p.LogCategoryId && o.ContestId == ContestQ.ContestId)); //http://www.albahari.com/nutshell/predicatebuilder.aspx var LogCateorySBSet = LogCategoryRepository.Queryable(); var LogCategoryQ2 = LogCateorySBSet.AsExpandable().Where(Predicate); //NOTE //Unfortunately there's no way to use Predicate<T> in EF linq since it's impossible to map it on SQL query. //This can be done with Expressions only because they can be parsed and converted to SQL //Predicate works on two tavles that have a relationship ONLY // LogCategoryQ2 is an Iqueryable with contes qualified //CallsignQ is an Iquertable qualified by the first char CallChar ICollection<CallGroupCall> CallGroupCalls = null; try { if (CallChar == "1") {//get all calls beginning with 1 through nine. var CallSignQ = CallSigns.SelectQueryable(false); CallGroupCalls = (from lc in CallSignQ join lq in LogQ1 on lc.CallSignId equals lq.CallsignId join lx in LogCategoryQ2 on lq.LogCategoryId equals lx.LogCategoryId where new[] { "1", "2", "3", "4", "5", "6", "7", "8", "9" }.Contains(lc.Call.Substring(0, 1)) select new CallGroupCall { CallSignID = lc.CallSignId, Call = lc.Call } ).OrderBy(x => x.Call).ToList(); } else { var CallSignQ = CallSigns.SelectQueryable(false).Where(c => c.Call.Substring(0, 1) == CallChar); CallGroupCalls = (from lc in CallSignQ join lq in LogQ1 on lc.CallSignId equals lq.CallsignId join lx in LogCategoryQ2 on lq.LogCategoryId equals lx.LogCategoryId select new CallGroupCall { CallSignID = lc.CallSignId, Call = lc.Call } ).OrderBy(x => x.Call).ToList(); } } catch (Exception ex) { // throw; } DataCalls.CallGroup = CallGroup; DataCalls.SelectedCall = SelectedCall; DataCalls.Calls = CallGroupCalls; CallSignRepository.SetSQLLogging(false); return Task.FromResult(DataCalls); }
//both GetCategorizedCalls and GetCategorizedCall2 methoda work. //GetCategorizedCalls2 uses the predicate builder along with asExpandable() //GetCategorizedCalls uses the Where extensions in DynamicLibrary.cs in the using System.Data.Entity namespace public static Task<DataCalls> GetCategorizedCalls2(this IRepository<Log> _LogRepository, LogCategory LogCategory, string SelectedContestName, string SelectedCall, Logqso.mvc.common.Enum.CallGroupEnum CallGroup, bool Disabled, string Username) { //The passed in _LogRepository has a DataContext from Dependency resolver //The Unit of work DataContext does not match the DataContext of the _Logrespository //SOLUTION: use the _LogRepository to get a LogRespositpry with the UoW DataContext //This allows the join below to have all of the expression trees point to the same DBContext. //This hook fixes a problem that should not occur, var LogRepository = _LogRepository.GetRepository<Log>(); DataCalls DataCalls = new DataCalls(); string CallChar = SelectedCall.Substring(0, 1); var CallSignRepository = LogRepository.GetRepository<CallSign>(); IQueryFluent<CallSign> CallSigns = CallSignRepository.Query(); CallSignRepository.SetSQLLogging(true); var LogCategoryRepository = LogRepository.GetRepository<LogCategory>(); IQueryFluent<LogCategory> LogCategorys = LogCategoryRepository.Query(); var LogCategoryQ = LogCategorys.SelectQueryable(false); //LogCategory LogCategoryQ = LogCategorys // .SelectQueryable(false).Where( // l => l.CatAssistedEnum == LogCategory.CatAssistedEnum && // l.CatBandEnum == LogCategory.CatBandEnum && // l.CatNoOfTxEnum == LogCategory.CatNoOfTxEnum && // l.CatOperatorEnum == LogCategory.CatOperatorEnum && // l.CatOperatorOverlayEnum == LogCategory.CatOperatorOverlayEnum && // l.CatPowerEnum == LogCategory.CatPowerEnum // ).SingleOrDefault(); var ContestRepository = LogRepository.GetRepository<Contest>(); IQueryFluent<Contest> Contests = ContestRepository.Query(); var ContestQ = Contests. SelectQueryable(false).Where(x => x.ContestName == SelectedContestName).FirstOrDefault(); IQueryFluent<Log> Logs = LogRepository.Query(); var LogQ1 = Logs // .Include(x => x.CallSign) //.Include(x=>x.LogCategory) .SelectQueryable(false).Where(x => x.ContestId == ContestQ.ContestId); String LogCategoryPredicate = "true "; //string.Empty; if (Disabled == false) {// business rules if (LogCategory.CatOperatorEnum == (int)CatOperatorEnum.SINGLE_OP) { LogCategory.CatNoOfTxEnum = (int)CatNoOfTxEnum.ALL; } else if (LogCategory.CatOperatorEnum == (int)CatOperatorEnum.MULTI_OP) { LogCategory.CatBandEnum = (int)CatBandEnum.ALL; if (LogCategory.CatNoOfTxEnum != (int)CatNoOfTxEnum.ONE) { LogCategory.CatPowerEnum = (int)CatPowerEnum.ALL; } LogCategory.CatAssistedEnum = (int)CatAssistedEnum.ALL; } else if (LogCategory.CatOperatorEnum == (int)CatOperatorEnum.CHECKLOG) { LogCategory.CatBandEnum = (int)CatBandEnum.ALL; LogCategory.CatPowerEnum = (int)CatPowerEnum.ALL; LogCategory.CatAssistedEnum = (int)CatAssistedEnum.ALL; LogCategory.CatNoOfTxEnum = (int)CatNoOfTxEnum.ALL; } else { //all } } else {//all disabled LogCategory.CatBandEnum = (int)CatBandEnum.ALL; LogCategory.CatPowerEnum = (int)CatPowerEnum.ALL; LogCategory.CatAssistedEnum = (int)CatAssistedEnum.ALL; LogCategory.CatNoOfTxEnum = (int)CatNoOfTxEnum.ALL; } if (LogCategory.CatAssistedEnum != (int)CatAssistedEnum.ALL) { LogCategoryPredicate = string.Format("{0} AND CatAssistedEnum = {1} ", LogCategoryPredicate, LogCategory.CatAssistedEnum); } if (LogCategory.CatBandEnum != (int)CatBandEnum.ALL) { LogCategoryPredicate = string.Format("{0} AND CatBandEnum = {1} ", LogCategoryPredicate, LogCategory.CatBandEnum); } if (LogCategory.CatNoOfTxEnum != (int)CatNoOfTxEnum.ALL) { LogCategoryPredicate = string.Format("{0} AND CatNoOfTxEnum = {1} ", LogCategoryPredicate, LogCategory.CatNoOfTxEnum); } if (LogCategory.CatOperatorEnum != (int)CatOperatorEnum.ALL) { LogCategoryPredicate = string.Format("{0} AND CatOperatorEnum = {1} ", LogCategoryPredicate, LogCategory.CatOperatorEnum); } if (LogCategory.CatOperatorOverlayEnum != null && LogCategory.CatOperatorOverlayEnum != (int)CatOperatorOverlayEnum.NONE) { LogCategoryPredicate = string.Format("{0} AND CatOperatorOverlayEnum = {1} ", LogCategoryPredicate, LogCategory.CatOperatorOverlayEnum); } if (LogCategory.CatPowerEnum != (int)CatPowerEnum.ALL) { LogCategoryPredicate = string.Format("{0} AND CatPowerEnum = {1} ", LogCategoryPredicate, LogCategory.CatPowerEnum); } var LogCateoryDBSet = LogCategoryRepository.Queryable(); var LogCategoryQ2 = LogCateoryDBSet.AsExpandable().Where(LogCategoryPredicate); ICollection<CallGroupCall> CallGroupCalls = null; try { if (CallChar == "1") {//get all calls beginning with 1 through nine. var CallSignQ = CallSigns.SelectQueryable(false); CallGroupCalls = (from lc in CallSignQ join lq in LogQ1 on lc.CallSignId equals lq.CallsignId join lx in LogCategoryQ2 on lq.LogCategoryId equals lx.LogCategoryId where new[] { "1", "2", "3", "4", "5", "6", "7", "8", "9" }.Contains(lc.Call.Substring(0, 1)) select new CallGroupCall { CallSignID = lc.CallSignId, Call = lc.Call } ).OrderBy(x => x.Call).ToList(); } else { var CallSignQ = CallSigns.SelectQueryable(false).Where(c => c.Call.Substring(0, 1) == CallChar); CallGroupCalls = (from lc in CallSignQ join lq in LogQ1 on lc.CallSignId equals lq.CallsignId join lx in LogCategoryQ2 on lq.LogCategoryId equals lx.LogCategoryId select new CallGroupCall { CallSignID = lc.CallSignId, Call = lc.Call } ).OrderBy(x => x.Call).ToList(); } DataCalls.CallGroup = CallGroup; DataCalls.SelectedCall = SelectedCall; DataCalls.Calls = CallGroupCalls; CallSignRepository.SetSQLLogging(false); } catch (Exception ex) { // throw; } #if false //var Log = LogQ.ToList(); //var cs = CallSignQ.Where(x => x.Call.Substring(0, 1) == CallChar).ToList(); //var res = (from lq in LogQ // join lc in CallSignQ on lq.CallsignId equals lc.CallSignId // where (lc.Call.Substring(0, 1) == CallChar) // select lq // ).ToList(); //var res2 = (from lq in LogQ // join lc in CallSignQ on lq.CallsignId equals lc.CallSignId // where (lc.Call.Substring(0, 1) == CallChar) // select lc // ).ToList(); if (CallChar == "1") {//get all calls beginning with 1 through nine. var CallGroupCalls = (from lc in CallSignQ join lq in LogQ1 on lc.CallSignId equals lq.CallsignId where new[] { "1", "2", "3", "4", "5", "6","7", "8", "9" }.Contains(lc.Call.Substring(0, 1) ) select new CallGroupCall { CallSignID = lc.CallSignId, Call = lc.Call } ).OrderBy(x => x.Call).ToList(); DataCalls.CallGroup = CallGroup; DataCalls.SelectedCall = SelectedCall; DataCalls.Calls = CallGroupCalls; } else { //// string SQLquery2012 = " SELECT " + //// "[Project1].[CallSignId] AS [CallSignId], " + ////" [Project1].[Call] AS [Call] " + //// "FROM ( SELECT " + //// " [Extent1].[CallSignId] AS [CallSignId], " + //// " [Extent1].[Call] AS [Call] " + //// " FROM [dbo].[CallSign] AS [Extent1] " + //// " INNER JOIN [dbo].[Log] AS [Extent2] ON [Extent1].[CallSignId] = [Extent2].[CallsignId] " + //// " WHERE (([Extent2].[ContestId] = 'CQWWSSB2014' ) AND ((SUBSTRING([Extent1].[Call], 0 + 1, 1)) = 'C' ) ) " + ////" ) AS [Project1]"; //// var CallGroupCalls12 = CallSigns.SqlQuery(SQLquery2012).ToList();; //// //var CallGroupCalls = CallGroupCalls12.ToList<CallGroupCall>(); //ef 6 sql2012 //declare @p__linq__0 varchar(50) //set @p__linq__0 = 'CQWWSSB2014' //declare @p__linq__1 varchar(50) //set @p__linq__1 = 'C' //SELECT // [Project1].[CallSignId] AS [CallSignId], // [Project1].[Call] AS [Call] // FROM ( SELECT // [Extent1].[CallSignId] AS [CallSignId], // [Extent1].[Call] AS [Call] // FROM [dbo].[CallSign] AS [Extent1] // INNER JOIN [dbo].[Log] AS [Extent2] ON [Extent1].[CallSignId] = [Extent2].[CallsignId] // WHERE (([Extent2].[ContestId] = @p__linq__0) OR (([Extent2].[ContestId] IS NULL) AND (@p__linq__0 IS NULL))) AND (((SUBSTRING([Extent1].[Call], 0 + 1, 1)) = @p__linq__1) OR ((SUBSTRING([Extent1].[Call], 0 + 1, 1) IS NULL) AND (@p__linq__1 IS NULL))) // ) AS [Project1] // ORDER BY [Project1].[Call] ASC var CallGroupCalls = (from lc in CallSignQ join lq in LogQ1 on lc.CallSignId equals lq.CallsignId where (lc.Call.Substring(0, 1) == CallChar) //where new[] { CallChar }.Contains(lc.Call.Substring(0, 1)) select new CallGroupCall { CallSignID = lc.CallSignId, Call = lc.Call } ).OrderBy(x => x.Call).ToList(); // var CallGroupCalls = CallGroupCalls1.ToList(); ////var CallGroupCalls = (from lc in CallSignQ //// join lq in LogQ1 on lc.CallSignId equals lq.CallsignId //// //where (lc.Call.Substring(0, 1) == CallChar) //// where new[] { CallChar }.Contains(lc.Call.Substring(0, 1)) //// select new CallGroupCall //// { //// CallSignID = lc.CallSignId, //// Call = lc.Call //// } //// ).OrderBy(x => x.Call).ToList(); //var CallGroupCalls = (from lq in LogQ1 // join lc in CallSignQ on lq.CallsignId equals lc.CallSignId // where (lc.Call.Substring(0, 1) == CallChar) // select new CallGroupCall // { // CallSignID = lq.CallSign.CallSignId, // Call = lq.CallSign.Call // } // ).OrderBy(x => x.Call).ToList(); DataCalls.CallGroup = CallGroup; DataCalls.SelectedCall = SelectedCall; DataCalls.Calls = CallGroupCalls; CallSignRepository.SetSQLLogging(false); } }
public static async Task<DataCalls> GetCategorizedCallsAsync(this IRepositoryAsync<Log> LogRepository, LogCategory LogCategory, string SelectedContestName, string CallChar, Logqso.mvc.common.Enum.CallGroupEnum CallGroup, bool Disabled, string Username) { return await GetCategorizedCalls(LogRepository, LogCategory, SelectedContestName, CallChar, CallGroup, Disabled, Username); }