static void Simple01()
{
List <Product> Lp = Product.GetSampleProducts();
List <Supplier> Lsp = Supplier.GetSampleSupplier();
Lp.Add(new Product("Noname"));
//Left outer JOIN
var result = from p in Lp.Where(x => x.Price <= 100 || x?.Price == null)
join s in Lsp on p.SupplierID equals s.SupplierID
into PS
from res in PS.DefaultIfEmpty()
select new
{
Sname = res?.Name ?? null,
Pname = p.Name,
Price = p?.Price ?? null,
};
foreach (var v in result.OrderBy(x => x?.Price).ThenBy(x => x?.Sname).ThenBy(x => x.Pname))
{
Console.WriteLine("{0} - {1:N2} - {2}", v.Pname, v.Price, v.Sname);
}
Console.ReadLine();
}
public ShipmentRoute GetShipmentHandlerId(int TradelaneShipemntId)
{
var Result = (from TS in dbContext.TradelaneShipments
join SH in dbContext.ShipmentHandlerMethods on TS.ShipmentHandlerMethodId equals SH.ShipmentHandlerMethodId into PS
from SH in PS.DefaultIfEmpty()
where TS.TradelaneShipmentId == TradelaneShipemntId
select new ShipmentRoute
{
MAWB = TS.MAWB,
RouteFrom = TS.DepartureAirportCode,
RouteTo = TS.DestinationAirportCode,
ShipemntHandlerMethodCode = SH.ShipmentHandlerMethodCode,
ShipmentHandlerMethodId = SH.ShipmentHandlerMethodId
}).FirstOrDefault();
return(Result);
}
public IEnumerable <MasterFunctionResult> GetAllADMasterFunction()
{
try
{
//var _data = _Context.ADMasterFunctions.Join(_Context.ADMasterFunctions, ADMasterFunction=> ADMasterFunction.ParentMasterFunctionId, ADMasterFunctionsNew => ADMasterFunctionsNew.MasterFunctionId,(ADMasterFunctions, ADMasterFunctionsNew) => new { ADMasterFunctions, ADMasterFunctionsNew }).Select(a=> new { a.ADMasterFunctions, ParentFunctionTitle= a.ADMasterFunctionsNew.FunctionTitle}).ToList();
var _data = (from MF in _Context.ADMasterFunctions
join MMF in _Context.ADMasterFunctions on MF.ParentMasterFunctionId equals MMF.MasterFunctionId into PS
from MMF in PS.DefaultIfEmpty()
select new { MF.MasterFunctionId, MF.FunctionTitle, MF.FunctionLink, MF.FunctionIcon, MF.FunctionIconColour, MF.ParentMasterFunctionId, ParentFunctionTitle = MMF.FunctionTitle, MF.Sequence, MF.IsActive, MF.EnterById, MF.EnterDate, MF.ModifiedById, MF.ModifiedDate });
List <MasterFunctionResult> objMasterFunctionResultList = new List <MasterFunctionResult>();
foreach (var Item in _data.ToList())
{
var _MasterFunctionResult = new MasterFunctionResult
{
MasterFunctionId = Item.MasterFunctionId,
FunctionTitle = Item.FunctionTitle,
FunctionLink = Item.FunctionLink,
FunctionIcon = Item.FunctionIcon,
FunctionIconColour = Item.FunctionIconColour,
ParentMasterFunctionId = Item.ParentMasterFunctionId,
ParentFunctionTitle = (Item.ParentFunctionTitle != null ? Item.ParentFunctionTitle : "Parent Menu"),
Sequence = Item.Sequence,
IsActive = Item.IsActive,
ActiveColor = "green",
ActiveIcon = "glyphicon glyphicon-ok"
};
if (_MasterFunctionResult.IsActive == false)
{
_MasterFunctionResult.ActiveColor = "red";
_MasterFunctionResult.ActiveIcon = "glyphicon glyphicon-remove";
}
objMasterFunctionResultList.Add(_MasterFunctionResult);
}
return(objMasterFunctionResultList);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
static void SimpleSavetoXLS()
{
var app = new Application {
Visible = false
};
try
{
Workbook workbook = app.Workbooks.Add();
Worksheet worksheet = app.ActiveSheet;
int row = 1;
List <Product> Lp = Product.GetSampleProducts();
List <Supplier> Lsp = Supplier.GetSampleSupplier();
Lp.Add(new Product("Noname"));
//Left outer JOIN
var result = from p in Lp.Where(x => x.Price <= 100 || x?.Price == null)
join s in Lsp on p.SupplierID equals s.SupplierID
into PS
from res in PS.DefaultIfEmpty()
select new
{
Sname = res?.Name ?? "",
Pname = p.Name,
Price = p?.Price ?? 999999.99m,
};
foreach (var res in result)
{
worksheet.Cells[row, 1].Value = res.Sname;
worksheet.Cells[row, 2].Value = res.Pname;
worksheet.Cells[row, 3].Value = res.Price;
row++;
}
workbook.SaveAs(Filename: exePath + "demo.xls",
FileFormat: XlFileFormat.xlWorkbookNormal);
}
catch (Exception ex) {
Console.WriteLine(ex.StackTrace);
}
finally
{
app.Application.Quit();
}
}
public MasterFunctionResult GetADMasterFunctionByID(long MasterFunctionId)
{
try
{
var _data = (from MF in _Context.ADMasterFunctions
join MMF in _Context.ADMasterFunctions on MF.ParentMasterFunctionId equals MMF.MasterFunctionId into PS
from MMF in PS.DefaultIfEmpty()
where MF.MasterFunctionId == MasterFunctionId
select new { MF.MasterFunctionId, MF.FunctionTitle, MF.FunctionLink, MF.FunctionIcon, MF.FunctionIconColour, MF.ParentMasterFunctionId, ParentFunctionTitle = MMF.FunctionTitle, MF.Sequence, MF.IsActive, MF.EnterById, MF.EnterDate, MF.ModifiedById, MF.ModifiedDate }).FirstOrDefault();
var _MasterFunctionResult = new MasterFunctionResult();
if (_data != null)
{
//string _ParentTitle = _Context.ADMasterFunctions.Where(a => a.MasterFunctionId == _data.ParentMasterFunctionId.Value).Select(a => a.FunctionTitle).FirstOrDefault();
_MasterFunctionResult.MasterFunctionId = _data.MasterFunctionId;
_MasterFunctionResult.FunctionTitle = _data.FunctionTitle;
_MasterFunctionResult.FunctionLink = _data.FunctionLink;
_MasterFunctionResult.FunctionIcon = _data.FunctionIcon;
_MasterFunctionResult.FunctionIconColour = _data.FunctionIconColour;
_MasterFunctionResult.ParentMasterFunctionId = _data.ParentMasterFunctionId;
//ParentFunctionTitle = (_ParentTitle != null ? _ParentTitle : "Parent Menu");
_MasterFunctionResult.Sequence = _data.Sequence;
_MasterFunctionResult.IsActive = _data.IsActive;
_MasterFunctionResult.ActiveColor = "green";
_MasterFunctionResult.ActiveIcon = "glyphicon glyphicon-ok";
if (_MasterFunctionResult.IsActive == false)
{
_MasterFunctionResult.ActiveColor = "red";
_MasterFunctionResult.ActiveIcon = "glyphicon glyphicon-remove";
}
}
return(_MasterFunctionResult);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}