linq to sql using crm early bound classes

2
votes

I am developing crm database based application. i want to get number of salesorderdetails under salesorder entity using linq to sql.

i am using subselect query for this. salesorder entity has new_sefer field. My query is 

(from d in context.SalesOrderDetailSet
                where context.SalesOrderSet.Where(s => s.new_Sefer.Id == Id)
                .Select(i => i.SalesOrderId).Contains(d.SalesOrderId.Id)
                select d).Count();

it throws below exception. I have also question about subselect queries

how can i write this sql query using linq to sql

select count(*) from salesorderdetail 
where salesorderId in (select salesorderId from salesorder 
                      where new_sefer = '750FEB6F-F742-E311-8F56-000C29F3049E')

"Invalid 'where' condition. An entity member is invoking an invalid property or method."

at Microsoft.Xrm.Sdk.Linq.QueryProvider.ThrowException(Exception exception) at Microsoft.Xrm.Sdk.Linq.QueryProvider.FindValidEntityExpression(Expression exp, String operation) at Microsoft.Xrm.Sdk.Linq.QueryProvider.FindValidEntityExpression(Expression exp, String operation) at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhereMethodCall(MethodCallExpression mce, FilterExpressionWrapper parentFilter, Func2 getFilter, BinaryExpression parent, Boolean negate) at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhereBoolean(String parameterName, Expression exp, FilterExpressionWrapper parentFilter, Func2 getFilter, List1 linkLookups, BinaryExpression parent, Boolean negate) at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhere(QueryExpression qe, String parameterName, Expression exp, List1 linkLookups) at Microsoft.Xrm.Sdk.Linq.QueryProvider.GetQueryExpression(Expression expression, Boolean& throwIfSequenceIsEmpty, Boolean& throwIfSequenceNotSingle, Projection& projection, NavigationSource& source, List1& linkLookups) at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute[TElement](Expression expression) at Microsoft.Xrm.Sdk.Linq.QueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.Count[TSource](IQueryable1 source) at Xrm.SalesOrderDetailOperations.SelectSalesOrderDetailBySeferId(XrmServiceContext context, Guid Id) in C:\SVN\Customers\Burulas\FlyDBOperations\FlyDBOperations\SalesOrderDetailOperations.cs:line 26 at FlyDBOperationsTest.SalesOrderDetailOperationsSalesOrderDetailOperationsTest.SelectSalesOrderDetailBySeferIdSelectSalesOrderDetailBySeferIdTest() in C:\SVN\Customers\Burulas\FlyDBOperations\FlyDBOperationsTest\SalesOrderDetailOperationsSalesOrderDetailOperationsTest.cs:line 82

2
c#.netsqllinqdynamics-crm-2011

2 Answers

6
votes

I believe this should work for you.

var count = (from d in context.SalesOrderDetailSet
                 join s in context.SalesOrderSet
                 on d.SalesOrderId.Id equals s.SalesOrderId
                 where s.new_Sefer.Id == Id
                 select d.SalesOrderId.Id).ToArray().Length;

Explination:

The linq expressions you are writing don't actually get converted into sql. They get converted into CRM's Query API so you have to write your linq statements in a way that the CRM linq provider can interpret. This leads to a lot of features and methods that exist in linq not being available (like Count()). Here is a link to the SDK that lists the supported features of linq.

So the query will join salesorderdeatil to the salesorder records based on the salesorderid which is the primary key of the salesorder entity. Then in your where clause you can filter based on the Id like you had. Finally in the select statement I only selected the ID of the record. I did this because it saves bandwith. If you don't do this, CRM will select every attribute of the entity and when you are retrieving large text fields it can cause performance issues. So only retrieve what you need. Finally since Count() isn't supported I just converted the results to an array and got the length from that.

-1
votes
from t in
(from t in db.salesorderdetail
where
    (from t0 in db.salesorderdetail
    where
      Convert.ToString(t0.new_sefer) == "750FEB6F-F742-E311-8F56-000C29F3049E"
    select new {
      t0.salesorderId
    }).Contains(new { t.salesorderId})
select new {
  Dummy = "x"
})
group t by new { t.Dummy } into g
select new {
  Column1 = (Int64?)g.Count()
}

I recomend you look for LINQER. Its a software that convert SQL QUERYS to LINQ.

http://www.sqltolinq.com/downloads