//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);
}
}
//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);
}
