QueryExpression多实体连接分页查询

已于 2022-05-25 15:11:37 修改
阅读量478 收藏
点赞数 1
分类专栏： C# QueryExpression 文章标签： c#
于 2022-05-25 15:08:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/finally_flx/article/details/124966277
版权 
var query = new QueryExpression("new_ord_billing");            

/// 分页参数设置
PagingInfo pageInfo = new PagingInfo();
pageInfo.Count = 20;
pageInfo.PageNumber = 1;
// 返回TotalRecordCount(数据总条数)时设置为true 否则TotalRecordCount会返回-1
pageInfo.ReturnTotalRecordCount = true;
query.PageInfo = pageInfo;

var shipNos = new string[] { "2019112824", "2020081805" };
// 查询条件设置
query.Criteria.AddCondition(new ConditionExpression("new_name", ConditionOperator.In, shipNos));
query.Criteria.AddCondition("new_type", ConditionOperator.Equal, 1);
FilterExpression filterOr = new FilterExpression
    {
        FilterOperator = LogicalOperator.Or,
        Filters =
           {
              new FilterExpression
              {
                 FilterOperator = LogicalOperator.And,
                 Conditions =
                 {
                    new ConditionExpression("modifiedon", ConditionOperator.OnOrAfter, DateTime.Now.AddDays(-1).ToShortDateString())
                 }
              },
              new FilterExpression
              {
                FilterOperator = LogicalOperator.And,
                Conditions =
                {
                   new ConditionExpression("createdon", ConditionOperator.On, DateTime.Now.ToShortDateString()),
                }
              }
          }
    };
query.Criteria.Filters.Add(filterOr);
query.ColumnSet = new ColumnSet(
    "new_bill_type",
    "new_amount",
    "new_account_id",
    "createdon",
    "new_type",
    "new_financial_month",
    "new_discount"
);
// 获取全部字段
// query.ColumnSet = new ColumnSet(true)

// 连接查询
query.LinkEntities.Add(new LinkEntity("new_ord_billing", "account", "new_account_id", "accountid", JoinOperator.LeftOuter));
query.LinkEntities[0].Columns.AddColumn("new_erp_number");
query.LinkEntities[0].EntityAlias = "ac";
query.LinkEntities[0].LinkCriteria.AddCondition("new_erp_number", ConditionOperator.Like, "%" + "CD001" + "%"); 

// 查询数据
var ec = OrganizationService.RetrieveMultiple(query);          

if (ec != null && ec.Entities.Count > 0)
{
	// ec.TotalRecordCount获取数据总条数
    foreach (var entity in ec.Entities)
    {
        ReturnModel model = new ReturnModel();
        // 单行文本
        model.BillType = entity.GetAttributeValue<string>("new_bill_type");
        // 货币类型
        if (entity.Contains("new_amount"))
        {
            model.Amount = entity.GetAttributeValue<Money>("new_amount").Value.ToString();
        }
        // 查找
        if (entity.GetAttributeValue<EntityReference>("new_account_id") != null)
        {                        
            model.AccountName = entity.GetAttributeValue<EntityReference>("new_account_id").Name;
        }
        // 连接查询获取
        if (entity.Attributes.Keys.Contains("ac.new_erpnumber") == true)
        {
            model.ErpNumber = ((AliasedValue)entity["ac.new_erpnumber"]).Value.ToString();
        }
        // 日期和时间
        model.CreatedOn = entity.GetAttributeValue<DateTime>("createdon").ToString();
        // 选项集
        if (entity.Contains("new_type"))
        {
            model.Type = entity.GetAttributeValue<OptionSetValue>("new_type").Value;
        }
        // 整数
        model.FinancialMonth = entity.GetAttributeValue<int>("new_financial_month");
        // 十进制数
        model.Discount = entity.GetAttributeValue<decimal>("new_discount");
    }
}

using System;
using Microsoft.Xrm.Sdk.Query;
using System.Collections.Generic;
using Microsoft.Xrm.Sdk;
using Newtonsoft.Json;

ConditionOperator

using System.Runtime.Serialization;

namespace Microsoft.Xrm.Sdk.Query
{
    //
    // 摘要:
    //     Describes the type of comparison for two values (or expressions) in a condition
    //     expression.
    [DataContract(Name = "ConditionOperator", Namespace = "http://schemas.microsoft.com/xrm/2011/Contracts")]
    public enum ConditionOperator
    {
        //
        // 摘要:
        //     The values are compared for equality. Value = 0.
        [EnumMember]
        Equal,
        //
        // 摘要:
        //     The two values are not equal. Value = 1.
        [EnumMember]
        NotEqual,
        //
        // 摘要:
        //     The value is greater than the compared value. Value = 2.
        [EnumMember]
        GreaterThan,
        //
        // 摘要:
        //     The value is less than the compared value. Value = 3.
        [EnumMember]
        LessThan,
        //
        // 摘要:
        //     The value is greater than or equal to the compared value. Value = 4.
        [EnumMember]
        GreaterEqual,
        //
        // 摘要:
        //     The value is less than or equal to the compared value. Value = 5.
        [EnumMember]
        LessEqual,
        //
        // 摘要:
        //     The character string is matched to the specified pattern. Value = 6.
        [EnumMember]
        Like,
        //
        // 摘要:
        //     The character string does not match the specified pattern. Value = 7.
        [EnumMember]
        NotLike,
        //
        // 摘要:
        //     TheThe value exists in a list of values. Value = 8.
        [EnumMember]
        In,
        //
        // 摘要:
        //     The given value is not matched to a value in a subquery or a list. Value = 9.
        [EnumMember]
        NotIn,
        //
        // 摘要:
        //     The value is between two values. Value = 10.
        [EnumMember]
        Between,
        //
        // 摘要:
        //     The value is not between two values. Value = 11.
        [EnumMember]
        NotBetween,
        //
        // 摘要:
        //     The value is null. Value = 12.
        [EnumMember]
        Null,
        //
        // 摘要:
        //     The value is not null. Value = 13.
        [EnumMember]
        NotNull,
        //
        // 摘要:
        //     The value equals yesterday’s date. Value = 14.
        [EnumMember]
        Yesterday,
        //
        // 摘要:
        //     The value equals today’s date. Value = 15.
        [EnumMember]
        Today,
        //
        // 摘要:
        //     The value equals tomorrow’s date. Value = 16.
        [EnumMember]
        Tomorrow,
        //
        // 摘要:
        //     The value is within the last seven days including today. Value = 17.
        [EnumMember]
        Last7Days,
        //
        // 摘要:
        //     The value is within the next seven days. Value = 18.
        [EnumMember]
        Next7Days,
        //
        // 摘要:
        //     The value is within the previous week including Sunday through Saturday. Value
        //     = 19.
        [EnumMember]
        LastWeek,
        //
        // 摘要:
        //     The value is within the current week. Value = 20.
        [EnumMember]
        ThisWeek,
        //
        // 摘要:
        //     The value is within the next week. Value = 21.
        [EnumMember]
        NextWeek,
        //
        // 摘要:
        //     The value is within the last month including first day of the last month and
        //     last day of the last month. Value = 22.
        [EnumMember]
        LastMonth,
        //
        // 摘要:
        //     The value is within the current month. Value = 23.
        [EnumMember]
        ThisMonth,
        //
        // 摘要:
        //     The value is within the next month. Value = 24.
        [EnumMember]
        NextMonth,
        //
        // 摘要:
        //     The value is on a specified date. Value = 25.
        [EnumMember]
        On,
        //
        // 摘要:
        //     The value is on or before a specified date. Value = 26.
        [EnumMember]
        OnOrBefore,
        //
        // 摘要:
        //     The value is on or after a specified date. Value = 27.
        [EnumMember]
        OnOrAfter,
        //
        // 摘要:
        //     The value is within the previous year. Value = 28.
        [EnumMember]
        LastYear,
        //
        // 摘要:
        //     The value is within the current year. Value = 29.
        [EnumMember]
        ThisYear,
        //
        // 摘要:
        //     The value is within the next year. Value = 30.
        [EnumMember]
        NextYear,
        //
        // 摘要:
        //     The value is within the last X hours. Value =31.
        [EnumMember]
        LastXHours,
        //
        // 摘要:
        //     The value is within the next X (specified value) hours. Value = 32.
        [EnumMember]
        NextXHours,
        //
        // 摘要:
        //     The value is within last X days. Value = 33.
        [EnumMember]
        LastXDays,
        //
        // 摘要:
        //     The value is within the next X (specified value) days. Value = 34.
        [EnumMember]
        NextXDays,
        //
        // 摘要:
        //     The value is within the last X (specified value) weeks. Value = 35.
        [EnumMember]
        LastXWeeks,
        //
        // 摘要:
        //     The value is within the next X weeks. Value = 36.
        [EnumMember]
        NextXWeeks,
        //
        // 摘要:
        //     The value is within the last X (specified value) months. Value = 37.
        [EnumMember]
        LastXMonths,
        //
        // 摘要:
        //     The value is within the next X (specified value) months. Value = 38.
        [EnumMember]
        NextXMonths,
        //
        // 摘要:
        //     The value is within the last X years. Value = 39.
        [EnumMember]
        LastXYears,
        //
        // 摘要:
        //     The value is within the next X years. Value = 40.
        [EnumMember]
        NextXYears,
        //
        // 摘要:
        //     The value is equal to the specified user ID. Value = 41.
        [EnumMember]
        EqualUserId,
        //
        // 摘要:
        //     The value is not equal to the specified user ID. Value = 42.
        [EnumMember]
        NotEqualUserId,
        //
        // 摘要:
        //     The value is equal to the specified business ID. Value = 43.
        [EnumMember]
        EqualBusinessId,
        //
        // 摘要:
        //     The value is not equal to the specified business ID. Value = 44.
        [EnumMember]
        NotEqualBusinessId,
        //
        // 摘要:
        //     No token name is specified <token xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
        //     <?Comment AL: Bug fix 5/30/12 2012-05-30T11:03:00Z Id='2?>internal<?CommentEnd
        //     Id='2' ?></token>.
        [EnumMember]
        ChildOf,
        //
        // 摘要:
        //     The value is found in the specified bit-mask value. Value = 46.
        [EnumMember]
        Mask,
        //
        // 摘要:
        //     The value is not found in the specified bit-mask value. Value = 47.
        [EnumMember]
        NotMask,
        //
        // 摘要:
        //     For internal use only. Value = 48.
        [EnumMember]
        MasksSelect,
        //
        // 摘要:
        //     The string contains another string. Value = 49.
        //     You must use the Contains operator for only those attributes that are enabled
        //     for full-text indexing. Otherwise, you will receive a generic SQL error message
        //     while retrieving data. In a Microsoft Dynamics 365 default installation, only
        //     the attributes of the KBArticle (article) entity are enabled for full-text indexing.
        [EnumMember]
        Contains,
        //
        // 摘要:
        //     The string does not contain another string. Value = 50.
        [EnumMember]
        DoesNotContain,
        //
        // 摘要:
        //     The value is equal to the language for the user. Value = 51.
        [EnumMember]
        EqualUserLanguage,
        //
        // 摘要:
        //     For internal use only.
        [EnumMember]
        NotOn,
        //
        // 摘要:
        //     The value is older than the specified number of months. Value = 53.
        [EnumMember]
        OlderThanXMonths,
        //
        // 摘要:
        //     The string occurs at the beginning of another string. Value = 54.
        [EnumMember]
        BeginsWith,
        //
        // 摘要:
        //     The string does not begin with another string. Value = 55.
        [EnumMember]
        DoesNotBeginWith,
        //
        // 摘要:
        //     The string ends with another string. Value = 56.
        [EnumMember]
        EndsWith,
        //
        // 摘要:
        //     The string does not end with another string. Value = 57.
        [EnumMember]
        DoesNotEndWith,
        //
        // 摘要:
        //     The value is within the current fiscal year . Value = 58.
        [EnumMember]
        ThisFiscalYear,
        //
        // 摘要:
        //     The value is within the current fiscal period. Value = 59.
        [EnumMember]
        ThisFiscalPeriod,
        //
        // 摘要:
        //     The value is within the next fiscal year. Value = 60.
        [EnumMember]
        NextFiscalYear,
        //
        // 摘要:
        //     The value is within the next fiscal period. Value = 61.
        [EnumMember]
        NextFiscalPeriod,
        //
        // 摘要:
        //     The value is within the last fiscal year. Value = 62.
        [EnumMember]
        LastFiscalYear,
        //
        // 摘要:
        //     The value is within the last fiscal period. Value = 63.
        [EnumMember]
        LastFiscalPeriod,
        //
        // 摘要:
        //     The value is within the last X (specified value) fiscal periods. Value = 0x40.
        [EnumMember]
        LastXFiscalYears,
        //
        // 摘要:
        //     The value is within the last X (specified value) fiscal periods. Value = 65.
        [EnumMember]
        LastXFiscalPeriods,
        //
        // 摘要:
        //     The value is within the next X (specified value) fiscal years. Value = 66.
        [EnumMember]
        NextXFiscalYears,
        //
        // 摘要:
        //     The value is within the next X (specified value) fiscal period. Value = 67.
        [EnumMember]
        NextXFiscalPeriods,
        //
        // 摘要:
        //     The value is within the specified year. Value = 68.
        [EnumMember]
        InFiscalYear,
        //
        // 摘要:
        //     The value is within the specified fiscal period. Value = 69.
        [EnumMember]
        InFiscalPeriod,
        //
        // 摘要:
        //     The value is within the specified fiscal period and year. Value = 70.
        [EnumMember]
        InFiscalPeriodAndYear,
        //
        // 摘要:
        //     The value is within or before the specified fiscal period and year. Value = 71.
        [EnumMember]
        InOrBeforeFiscalPeriodAndYear,
        //
        // 摘要:
        //     The value is within or after the specified fiscal period and year. Value = 72.
        [EnumMember]
        InOrAfterFiscalPeriodAndYear,
        //
        // 摘要:
        //     The record is owned by teams that the user is a member of. Value = 73.
        [EnumMember]
        EqualUserTeams,
        //
        // 摘要:
        //     The record is owned by a user or teams that the user is a member of. Value =
        //     74.
        [EnumMember]
        EqualUserOrUserTeams,
        //
        // 摘要:
        //     Returns all child records below the referenced record in the hierarchy. Value
        //     = 76.
        [EnumMember]
        Under,
        //
        // 摘要:
        //     Returns all records not below the referenced record in the hierarchy. Value =
        //     77.
        [EnumMember]
        NotUnder,
        //
        // 摘要:
        //     Returns the referenced record and all child records below it in the hierarchy.
        //     Value = 79.
        [EnumMember]
        UnderOrEqual,
        //
        // 摘要:
        //     Returns all records in referenced record's hierarchical ancestry line. Value
        //     = 75.
        [EnumMember]
        Above,
        //
        // 摘要:
        //     Returns the referenced record and all records above it in the hierarchy. Value
        //     = 78.
        [EnumMember]
        AboveOrEqual,
        //
        // 摘要:
        //     When hierarchical security models are used, Equals current user or their reporting
        //     hierarchy. Value = 80.
        [EnumMember]
        EqualUserOrUserHierarchy,
        //
        // 摘要:
        //     When hierarchical security models are used, Equals current user and his teams
        //     or their reporting hierarchy and their teams. Value = 81.
        [EnumMember]
        EqualUserOrUserHierarchyAndTeams,
        [EnumMember]
        OlderThanXYears,
        [EnumMember]
        OlderThanXWeeks,
        [EnumMember]
        OlderThanXDays,
        [EnumMember]
        OlderThanXHours,
        [EnumMember]
        OlderThanXMinutes
    }
}
Kalinda Sharma
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用Expression进行动态排序分页
weixin_30307921的博客
05-12 917
Expression动态查询、分页 Expression，表达式树，以lamda表达式创建，就以表达式目录树的形式将强类型的lambda表达式标识为数据结构。 排序 /// <summary> /// 根据条件排序和查询 /// </summary> /// <typeparam name="TKey"...
crm快速开发之QueryExpression
菜刀居士的专栏
07-07 3989
crm快速开发之QueryExpression
分享动态拼接Expression表达式组件及原理
weixin_34406061的博客
06-30 816
前言 LINQ大家都知道，用起来也还不错，但有一个问题，当你用Linq进行搜索的时候，你是这样写的 var query = from user in db.Set<User>() where user.Username == "xxxx" select u...
7. Query Expressions（查询表达式）
weixin_34354173的博客
11-21 153
【返回目录】 查询表达式提供了与SQL这样的关系化和分级的查询语言相类似的语言集成的语法。一个查询表达式是以from子句开头以select或者group子句结束，这个初始的from子句可以在其后跟随任意多个from、let、where或者join子句。 那么查询表达式中的这些子句都是做什么的呢？from子句是一个引入了一个覆盖整个序列的范围变量的生成器；let子句计算一个值并传入一个标识符来显...
关于Expression表达式树的拼接
weixin_30325071的博客
06-06 296
最近在做项目中遇到一个问题,需求是这样的: 我要对已经存在的用户进行检索,可以根据用户的id 或者用户名其中的一部分字符来检索出来,这样就出现了三种情况 只有id,只有用户名中一部字符,或者全部都有. 我们用的MVC+EF5.0的框架,在BLL层进行查询的 时候需要构建lambda表达式来作为查询条件,但是,我们怎么来构建lambda来确定查询的条件呢?我们知道Express<Func&...
多个实体类进行分页查询
筱夜宫主
10-24 561
1>这种查询只能查询3个及3个以下的实体类。 [code="java"] public PaginationSupport fingEntity(int type, int pageSize, int startIndex) { String jpql = " from AA a,BB b,CC c"; jpql += " where a.id=b.id and b.e_id=...
dynamics365 QueryExpression、FilterExpression、ConditionExpression、OrderExpression、PagingInfo等类库基本信息用法
weixin_43235056的博客
11-12 1551
QueryExpression类库的属性 声明QueryExpression添加它的基本属性，调用组织服务的RetrieveMultiple方法进行查询 属性名 作用 读法 EntityName 指定将检索哪种类型的实体。 一个查询表达式仅检索一个实体类型集合。 ColumnSet 指定要检索的属性（列）的集合。 Criteria 指定复杂条件和逻辑筛选器表达式，用于筛选查询结果。 Distinct 指定查询的结果是否包含重复记录。 LinkEntities 指定多个
VirtualTables:在Dataverse中创建虚拟实体
04-06
总结起来，创建Dataverse中的虚拟实体是通过C#进行集成开发的重要部分，它涉及连接到Dataverse，定义实体元数据，设置数据源，定义查询表达式，并管理权限。虚拟实体的使用极大地扩展了Dataverse的能力，允许无缝...
crm-connection-testing:使用XRM工具包连接到Microsoft Dynamics 365 CRM实例的概念证明
03-17
5. **查询数据**：XRM工具包提供了`QueryExpression`和` LINQ 查询`两种方式来获取CRM中的数据。例如，以下是如何使用`QueryExpression`查找所有联系人： ```csharp var query = new QueryExpression("contact"); ...
behaviours-js:用JavaScript编写的行为框架实现了行为优先设计和队列映射架构
04-11
var ( BusinessController , BehaviourConstructor , ModelEntity , QueryExpression , ComparisonOperators } = require ( 'behaviours' ) ; var define = require ( 'define-js' ) ; // 1: define data controller...
dynamics Crm QueryExpression 的用法
qq_46599963的博客
12-09 1212
dynamics Crm QueryExpression 的用法
Dynamics 365 多选选项集
一只不知疲倦的学习猿
06-16 332
dynamics 365 多选选项集
sql高级
River741132472的博客
03-15 321
1:去重查交集 select supplier_name supplierName, supplier_url supplierUrl, supplier_no supplierNo, COUNT(1) as num from ware_supplier where product_no in &lt;foreach collection="productStrs" index="index"...
解决 mysql多表联合查询时出现的分页问题
weixin_30304375的博客
07-08 3089
mysql一对多分页问题 部门表：tbl_dept 员工表：tbl_emp 数据库sql文件 CREATE DATABASE /*!32312 IF NOT EXISTS*/`ssm-crud` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `ssm-crud`; /*Table structure for table `tbl_dep...
CRM QueryExpression的分页查询
Gao996的博客
11-19 3281
#大于5000条数据分页查询QueryExpression 附上一个链接https://blog.csdn.net/y_f123/article/details/51307526 public static void QueryPage(IOrganizationService service, QueryExpression query, Action<EntityCollection...
Cris 学 SpringMVC（二）：使用 pojo 作为方法参数
04-10 220
使用 POJO 对象绑定请求参数值 Spring MVC 会按请求参数名和 POJO 属性名进行自动匹 配，自动为该对象填充属性值。支持级联属性 测试代码 /* * springMVC 会先按照请求参数名和pojo 的属性名自动匹配 * 并且自动为该对象填充属性值，而且支持级联属性，十分方便 */ @RequestMapping("tes...
C# Linq查询、where 扩展查询以及QueryExpression分页查询
m0_70735467的博客
08-11 797
C# 常用的三种查询方式
Dynamics CRM QueryExpression 查询方法
DanielChen的博客
11-12 2409
QueryExpression query = new QueryExpression() { EntityName = "", ColumnSet = new ColumnSet(true), NoLock = true, Criteria = { Conditions = ...
虚拟实体调用retrieveMultiple插件的查询列怎么设置
最新发布
05-31
虚拟实体的查询列的设置方式与实体是一样的，你需要在查询表达式中指定需要查询的列。以下是示例代码： ``` Entity lookup = (Entity)context.InputParameters["Target"]; //获取虚拟实体中的LookUp字段 EntityReference lookupRef = (EntityReference)lookup["your_lookup_field"]; //根据LookUp字段中的实体名称和Id构建查询表达式 QueryExpression query = new QueryExpression(lookupRef.LogicalName); query.ColumnSet = new ColumnSet("column1", "column2", "column3"); //指定需要查询的列 query.Criteria.AddCondition(new ConditionExpression("your_entity_id_field", ConditionOperator.Equal, lookupRef.Id)); EntityCollection results = service.RetrieveMultiple(query); ``` 在上面的代码中，你可以看到我在查询表达式中指定了需要查询的列，即"column1", "column2", "column3"。你可以根据实际需求来指定需要查询的列。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值