LINQ Query not working in silver light project

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<SpendPendingByCommodity>();

 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<SpendPendingByCommodity>();
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<EntityQueriedEventArgs<SpendPendingByCommodity>>(qry_Completed);

           void qry_Completed(object sender, EntityQueriedEventArgs<SpendPendingByCommodity> 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

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??