Print Page | Close Window

LINQ Query not working in silver light project

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2745
Printed Date: 13-Apr-2025 at 2:40am


Topic: LINQ Query not working in silver light project
Posted By: Jeena
Subject: LINQ Query not working in silver light project
Date 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<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


Replies:
Posted By: DenisK
Date 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?

Posted By: Jeena
Date 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??

Posted By: DenisK
Date 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 - http://drc.ideablade.com/xwiki/bin/view/Documentation/query-101-examples


Print Page | Close Window