This is my current code setup
The SQL code I am trying to port to LINQ is
INSERT INTO @PFCollateral
SELECT a.PortfolioId,b.SecurityCode,Sum(b.Quantity)
ColQuantity,0,0,0,0,b.RestrictionType,b.SecurityType
FROM portfoliostrategy a
INNER join SecurityCollateral b ON a.TrxNo = b.TrxNo
WHERE b.ResDate<= @AsOfDate AND a.PortfolioId >= @FromPF and a.PortfolioId <= @ToPF
GROUP BY a.PortfolioId, b.SecurityCode, b.RestrictionType, b.SecurityType
The LINQ query I wrote is as follows:
var PFCollateral = (from a in metaData.PortfolioStrategy
from b in metaData.SecurityCollateral
where
a.TrxNo == b.TrxNo &&
b.ResDate <= new DateTime(2008 / 1 / 1) &&
a.PortfolioNo >= 1 &&
a.PortfolioNo <= 1
group b by new
{
b.SecurityCode,
b.RestrictionType,
b.SecurityType,
a.PortfolioNo
} into g
select new
{
PortfolioID = g.Key.PortfolioNo ,
SecurityCode = g.Key.SecurityCode,
ColQuantity = g.Sum(p => p.Quantity),
Column1 = 0,
Column2 = 0,
Column3 = 0,
Column4 = 0,
RestrictionType = g.Key.RestrictionType ,
SecurityType = g.Key.SecurityType
});
MessageBox.Show(PFCollateral.Count().ToString());
At run time I get the following error
Message =An item with the same key has already been added.
Stack Trace =
at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.ProjectionListExpression.AddElement(String name, Object element, Type elementType, Int32 elementIndex)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.ProjectionListExpression.AddExpressionElement(String elementName, Type elementType, Expression elementToAdd, Int32 elementIndex, IElementCreatorCore generatedCodeCreator, ITemplateGroupSpecificCreator frameworkElementCreator, MappingTracker trackedMappings, FunctionMappingsContainer functionMappings)
at SD.LLBLGen.Pro.LinqSupportClasses.LinqUtils.CoerceLinqExpressionToProjectionListExpression(Expression toCoerce, IElementCreatorCore generatedCodeElementCreator, ITemplateGroupSpecificCreator frameworkElementCreator, MappingTracker trackedMappings, FunctionMappingsContainer functionMappings)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleProjectionExpression(ProjectionExpression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleAggregateExpression(AggregateExpression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
at BNIS.AMS.WIN.PostCashDeposite.Form1.TestLinq() in C:\AMS\BNIS.AMS.WIN.PostCashDeposite\Form1.cs:line 745
at BNIS.AMS.WIN.PostCashDeposite.Form1.Form1_Load(Object sender, EventArgs e) in C:\AMS\BNIS.AMS.WIN.PostCashDeposite\Form1.cs:line 139
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at BNIS.AMS.WIN.PostCashDeposite.Program.Main() in C:\AMS\BNIS.AMS.WIN.PostCashDeposite\Program.cs:line 18
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Now I managed to solve this by changing the LINQ query select projection to be
Column1 = (g.Key.PortfolioID - g.Key.PortfolioID),
Column2 = (g.Key.PortfolioID - g.Key.PortfolioID),
Column3 = (g.Key.PortfolioID - g.Key.PortfolioID),
Column4 = (g.Key.PortfolioID - g.Key.PortfolioID),
I dont understand why this is happening ???
