DevForce Community Forum : LINQ Query not working in silver light project

LINQ Query not working in silver light project : Hi Jeena;Try writing your query...

Mon, 13 Jun 2011 18:52:14 -700

Author: DenisK
Subject: 2745
Posted: 13-Jun-2011 at 6:52pm

Hi Jeena;

Try writing your query as follows.

var query = from managerSpends in _mgr.vwManagerSpends

where (managerSpends.ID == App.DBUser.UserID && managerSpends.HasWrite == true)

group managersSpends by managerSpends.Date.Value.Month into g

select new {

date = g.Key

customerExpenses = g.sum(ms => ms.expenseByCustomer),

employeeExpenses = g.Sum(ms => ms.expenseByEmployee),

};

You can also refer to this page for more LINQ query examples. http://drc.ideablade.com/xwiki/bin/view/Documentation/query-101-examples

Edited by DenisK - 15-Jun-2011 at 4:12pm

LINQ Query not working in silver light project : Hi i have created a view and my...

Thu, 09 Jun 2011 23:24:18 -700

Author: Jeena
Subject: 2745
Posted: 09-Jun-2011 at 11:24pm

Hi

i have created a view and my data is something like this now..

USerID	pid	date	expenseByCustomer	expenseByEmployee	total exp	Has read	Has write
1	3	12/15/2010 0:00	5423311.7	2060206.59	7483518.29	1	1
1	3	1/15/2011 0:00	6511198.06	3287308.85	9798506.91	1	1
1	3	2/15/2011 0:00	6776986.05	1464081.51	8241067.56	1	1
1	3	3/15/2011 0:00	8664801.13	2477348.81	11142149.94	1	1

Now i need to take the data as follows

select SUM(expenseByCustomer),SUM(expenseByEmployee),MONTH(DATE) from VW_NS_SPEND_PENDING_BY_USER where MC_USER_ID=1
and HAS_WRITE=1 group by month(DATE)

i was trying to get the data like this

from i in
from managerSpends in _mgr.vwManagerSpends
where (managerSpends.ID == App.DBUser.UserID && managerSpends.HasWrite == true)
select new { managerSpends.DATE, managerSpends.expenseByCustomer, managerSpends.expenseByEmployee })
group i by new { i.DATE.Value.Month} into g
select new
{
//i expected like i can take the data here using sum function but am not getting it here
};

can somebody help me to solve this??

LINQ Query not working in silver light project : Hi Jeena;Your query looks reasonable....

Thu, 09 Jun 2011 11:01:01 -700

Author: DenisK
Subject: 2745
Posted: 09-Jun-2011 at 11:01am

Hi Jeena;

Your query looks reasonable. Can you see if you can dig in more into the MethodAccessException and post the complete message and stack trace?

LINQ Query not working in silver light project : Hi allIs it possible to do this...

Tue, 07 Jun 2011 07:36:39 -700

Author: Jeena
Subject: 2745
Posted: 07-Jun-2011 at 7:36am

Hi all

Is it possible to do this query using LINQ in ideablade for my silver light project using MVVM pattern

"select SUM(sp.ITEM_SPEND) as ITEM_SPEND ,SUM(SUPPLIER_SPEND) as SUPPLIER_SPEND,
SUM(SPEND_TOTAL) as SPEND_TOTAL,sp.SPEND_DATE from TBL_SPEND_PENDING_BY_TCHS sp
INNER JOIN TBL_NS_USER_TCHS cm ON sp.MDW_TCHS_ID = cm.MDW_TCHS_ID
where cm.MC_USER_ID = 783 and sp.MDW_TCHS_ID in (3,135)
AND sp.spend_date between DateAdd(yy,-1,getdate()) and getDate()
group by sp.SPEND_DATE"

   //Query to create TBL_SPEND_PENDING_BY_TCHS
   CREATE TABLE [dbo].[TBL_SPEND_PENDING_BY_TCHS](
    [TCHS_SPEND] [int] IDENTITY(1,1) NOT NULL,
    [MDW_TCHS_ID] [int] NOT NULL,
    [SPEND_DATE] [smalldatetime] NULL,
    [ITEM_SPEND] [decimal](18, 2) NOT NULL,
    [SUPPLIER_SPEND] [decimal](18, 2) NOT NULL,
    [SPEND_TOTAL] AS ([ITEM_SPEND]+[SUPPLIER_SPEND]),
    [SPEND_TREND] [bit] NULL)

   //Query to create TBL_NS_USER_TCHS
CREATE TABLE [dbo].[TBL_NS_USER_TCHS](
    [MC_USER_ID] [int] NOT NULL,
    [MDW_TCHS_ID] [int] NOT NULL,
    [HAS_READ] [bit] NOT NULL,
    [HAS_WRITE] [bit] NOT NULL)

i have tried the following way

//SpendPendingByCommodity it maps to TBL_SPEND_PENDING_BY_TCHS table
//UserCommodities maps to TBL_NS_USER_TCHS

selectedCommoditiesSpendByUser = new ObservableCollection();

var selectedCommoditiesByUserQuery = from TBL_NS_USER_TCHS in _mgr.UserCommodities
                                   join TBL_SPEND_PENDING_BY_TCHS in _mgr.SpendPendingByCommodities
                                   on TBL_NS_USER_TCHS.CommodityID equals
                                   TBL_SPEND_PENDING_BY_TCHS.CommodityID
                                   where TBL_NS_USER_TCHS.UserID == App.DBUser.UserID &&                                                           selectedCommoditiesByUser.Contains(TBL_SPEND_PENDING_BY_TCHS.CommodityID)
                                  && TBL_SPEND_PENDING_BY_TCHS.SpendDate< DateTime.Now.AddYears(-1)
                                   && TBL_SPEND_PENDING_BY_TCHS.SpendDate>DateTime.Now.Date
                                   group TBL_SPEND_PENDING_BY_TCHS by TBL_SPEND_PENDING_BY_TCHS.SpendDate into result
                                   select new
                                   {

                                    }

i was not getting results.fields name so i changed the query to as follows and tried

selectedCommoditiesSpendByUser = new ObservableCollection();
var selectedCommoditiesByUserQuery = from TBL_NS_USER_TCHS in _mgr.UserCommodities
                                                     join TBL_SPEND_PENDING_BY_TCHS in _mgr.SpendPendingByCommodities
                                                     on TBL_NS_USER_TCHS.CommodityID equals
   TBL_SPEND_PENDING_BY_TCHS.CommodityID
                                                     where TBL_NS_USER_TCHS.UserID == App.DBUser.UserID &&
   TBL_SPEND_PENDING_BY_TCHS.CommodityID == 3
                                                     select TBL_SPEND_PENDING_BY_TCHS;

            var qry = selectedCommoditiesByUserQuery.ExecuteAsync();
            qry.Completed += new EventHandler>(qry_Completed);

           void qry_Completed(object sender, EntityQueriedEventArgs e)
         {
            foreach (SpendPendingByCommodity item in e.Results)
            {
                selectedCommoditiesSpendByUser.Add(item);
            }
        }

This is doing while selecting the commodity code by the user on a rad grid's selected item change command..On qry_Completed am getting the error as e.Results' threw an exception of type 'System.MethodAccessException'..

am i doing anything wrong please help me to solve this issue?

Thanks
Jeena