sorting the grid

Hi,

Iam working on sorting the grid remotely.

Iam using the folllowing query

SELECT value a, ROW_NUMBER() OVER (ORDER BY" + " " + SortExpression + ") As RowNumber FROM XSP_AssetList_V AS a WHERE a.AccountID = GUID'" + AccountID + "'";

is there any linq equivalanet for rownumber() over orderby()

but iam getting an error

System.Data.EntitySqlException: The query syntax is not valid., near identifier 'OVER', line 1, column 241. at System.Data.Common.EntitySql.CqlParser.yyerror(String s) at System.Data.Common.EntitySql.CqlParser.yyparse() at System.Data.Common.EntitySql.CqlParser.internalParseEntryPoint() at System.Data.Common.EntitySql.CqlParser.Parse(String query) at System.Data.Common.EntitySql.CqlQuery.Parse(String query, ParserOptions parserOptions) at System.Data.Common.EntitySql.CqlQuery.Compile(DbCommandTree builderTree, String queryText, Perspective perspective, ParserOptions parserOptions, Dictionary2 parameters, Dictionary2 variables) at System.Data.Objects.EntitySqlQueryState.Parse(DbCommandTree parseTree) at System.Data.Objects.ELinq.ExpressionConverter.TranslateInlineQueryOfT(ObjectQuery inlineQuery) at System.Data.Objects.ELinq.ExpressionConverter.ConstantTranslator.TypedTranslate(ExpressionConverter parent, ConstantExpression linq) at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator1.Translate(ExpressionConverter parent, Expression linq) at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator1.Translate(ExpressionConverter parent, Expression linq) at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator1.Translate(ExpressionConverter parent, Expression linq) at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) at System.Data.Objects.ELinq.ExpressionConverter.Convert() at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at GetPrintersGrid.buildGrid(Int32 start, Int32 limit, String sortfield, String dir) in c:\Work\Shared Services\FMP\Main\Dropthings\App_Code\GetPrintersGrid.cs:line 162

the same query when i execute in management studio it is working fine.Can you please help me out in this issue.

[WebMethod(EnableSession = true)] [ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true, XmlSerializeString = false)]

public PagedResult<FMPAsset> buildGrid(int start, int limit, string sortfield, string dir)
{


 Guid AccountID = (Guid)Session["AccountID"];


 string SortExpression = sortfield + " " + (!String.IsNullOrEmpty(dir) ? dir : String.Empty);  
 string whereClause = "SELECT value a, ROW_NUMBER() OVER (ORDER BY" + " " + SortExpression + ") As RowNumber FROM XSP_AssetList_V AS a WHERE a.AccountID = GUID'" + AccountID + "'";

 List<FMPAsset> fmpAssets = new List<FMPAsset>();



 using (XSPAssetModel.XSPAssetEntities assetEntities = new XSPAssetEntities(b.BuildEntityConnectionString1("XSMDSN")))
 {
  ObjectQuery<XSP_AssetList_V> assets = new ObjectQuery<XSP_AssetList_V>(whereClause, assetEntities);

  var assetPage = assets.Skip(start).Take(limit);


  currentAssets = assetPage.ToList();
  int currentAssetsCount = currentAssets.Count;
  string imgprefix = System.Configuration.ConfigurationManager.AppSettings["ImgPrefix"];
  char[] separators = { '/' };
  string appname = "";
  int lastloc = imgprefix.Substring(0, imgprefix.Length - 1).LastIndexOfAny(separators);
  if (lastloc > 6)
  {
   appname = imgprefix.Substring(lastloc + 1);
  }


  FMPAsset asset = new FMPAsset();



  XSPPrinterMarkerSupplyModel.XSPPrinterMarkerSupplyEntities markerCtx = new XSPPrinterMarkerSupplyModel.XSPPrinterMarkerSupplyEntities(b.BuildEntityConnectionString1("XSMDSN"));

  for (int x = 0; x < currentAssetsCount; x++)
  {
   asset = new FMPAsset();

   asset.AssetID = currentAssets[x].AssetID.ToString();
   asset.PricePlanID = currentAssets[x].PricePlanID.ToString();
   asset.AssociationID = currentAssets[x].AssociationID;
   asset.ModelName = currentAssets[x].ModelName;
   asset.ResponsibilityForAction = currentAssets[x].ResponsibilityForAction;
   asset.IPAddress = (String.IsNullOrEmpty(currentAssets[x].PrinterIPAddress)) ? "No IP" : currentAssets[x].PrinterIPAddress; ;

   if (currentAssets[x].InScope)
   {
    asset.InScope = b.GetString("SDE_YES");
   }
   else
   {
    asset.InScope = b.GetString("SDE_NO");
   }

   asset = SetStatus(appname, asset, x);

   asset.PricePlanName = currentAssets[x].Program;
   asset.PricePlanDescription = currentAssets[x].PricePlanDescription;
   asset.ServicePlanName = currentAssets[x].ServicePlanName;

   if (currentAssets[x].PrinterSerialNumber != null)
   {
    asset.PrinterSerialNumber = currentAssets[x].PrinterSerialNumber;
   }
   else
   {
    asset.PrinterSerialNumber = "-";
   }


   if (this.b.UseChargebackDescription && !String.IsNullOrEmpty(currentAssets[x].CustomerChargebackDescription) && currentAssets[x].CustomerChargebackDescription != "Generated by OUT Integration")
   {
    asset.Customer = currentAssets[x].CustomerChargebackDescription;

    if (asset.Customer.IndexOf(Environment.NewLine) > -1)
    {
     asset.Customer = asset.Customer.Substring(0, asset.Customer.IndexOf(Environment.NewLine));
    }
   }
   else
   {
    asset.Customer = currentAssets[x].CustomerChargeBackEntryName;
   }

   if (this.b.UsePricePlanDescription && !String.IsNullOrEmpty(currentAssets[x].PricePlanDescription))
   {
    asset.Program = currentAssets[x].PricePlanDescription;
    if (asset.Program.IndexOf(Environment.NewLine) > -1)
    {
     asset.Program = asset.Program.Substring(0, asset.Program.IndexOf(Environment.NewLine));
    }
   }
   else
   {
    asset.Program = currentAssets[x].Program;
   }

   asset.BlackPct = -3;
   asset.CyanPct = -3;
   asset.MagentaPct = -3;
   asset.YellowPct = -3;

   Guid id = currentAssets[x].AssetID;

   asset = SetCMYKvalues(asset, x);

   BuilldImpressionsValues(currentAssets[x], ref asset);
   fmpAssets.Add(asset);
  }

  var totalAssetCount = assets.Count();
  var y = new PagedResult<FMPAsset>();
  y.Records = fmpAssets;
  y.TotalCount = totalAssetCount;
  return y;




 }

}

ansaurus

tags:

views:

answers:

related questions