♈️ 🥝 🤞🏿 TypeScript中的类型化OData查询 🐗 🐢 🛬

传统上，对数据的OData查询表示为简单字符串，无需在编译过程中进行类型检查或不提供IntelliSense支持，此外，开发人员还必须学习查询语言的语法。本文介绍了TsToOdata库，该库将查询转换为方便的语言构造，并类似地应用于类和方法。您可以使用TypeScript关键字和熟悉的运算符来创建类型化查询。

TsToOdata是TypeScript的库。它与C＃的LINQ相似，但与后者不同，它仅用于OData查询。对于创建查询的开发人员而言，TsToOdata最明显的部分是查询表达式。查询表达式使用声明性语法，因此开发人员可以编写需要完成的操作，而无需指定如何执行。使用查询语法，您可以使用最少的程序代码来过滤，组织和分组数据源中的数据。

安装TsToOdata

npm install ts2odata

创建数据模型

首先，我们需要获取OData模式到TypeScript类的映射。
第一步是首先从EDMX Json获取模式。您可以为此使用OdataToEntity库。

IEdmModel edmModel;
using (var reader = XmlReader.Create("edmx_schema.xml"))
    edmModel = CsdlReader.Parse(reader);

var generator = new OeJsonSchemaGenerator(edmModel);
using (var utf8Json = new MemoryStream())
{
    generator.Generate(utf8Json);
    utf8Json.Position = 0;
    File.WriteAllBytes("json_schema.json", utf8Json.ToArray());
}

Json TypeScript. quicktype.
.

import { EntitySet, OdataContext } from 'ts2odata';
import * as oe from './order';

export class OrderContext extends OdataContext<OrderContext> {
    Categories = EntitySet.default<oe.Category>();
    Customers = EntitySet.default<oe.Customer>();
    OrderItems = EntitySet.default<oe.OrderItem>();
    OrderItemsView = EntitySet.default<oe.OrderItemsView>();
    Orders = EntitySet.default<oe.Order>();
}

let context: OrderContext = OdataContext.create(OrderContext, 'http://localhost:5000/api');

context.Orders;
//http://localhost:5000/api/Orders

context.Orders.select(o => { return { p: o.Name } });
//http://localhost:5000/api/Orders?$select=Name

context.Orders.orderby(i => i.Id);
//http://localhost:5000/api/Orders?$orderby=Id

context.Orders.orderbyDescending(i => i.Id);
//http://localhost:5000/api/Orders?$orderby=Id desc

context.Orders.filter(o => o.Date.getFullYear() == 2016);
//http://localhost:5000/api/Orders?$filter=year(Date) eq 2016

context.Orders.expand(o => o.Items);
//http://localhost:5000/api/Orders?$expand=Items

context.Customers.expand(c => c.Orders).thenExpand(o => o.Items);
//http://localhost:5000/api/Customers?$expand=Orders($expand=Items)

context.Orders.orderby(i => i.Id).skip(2);
//http://localhost:5000/api/Orders?$orderby=Id&$skip=2

context.Orders.orderby(i => i.Id).top(3);
//http://localhost:5000/api/Orders?$orderby=Id&$top=3

context.OrderItems.groupby(i => { return { Product: i.Product } });
//localhost:5000/api/OrderItems?$apply=groupby((Product))

context.OrderItems.groupby(i => { return { OrderId: i.OrderId, Status: i.Order.Status } })
    .select(g => {
        return {
            orderId: g.key.OrderId,
            avg: g.average(i => i.Price),
            dcnt: g.countdistinct(i => i.Product),
            max: g.max(i => i.Price),
            max_status: g.max(_ => g.key.Status),
            min: g.min(i => i.Price),
            sum: g.sum(i => i.Price),
            cnt: g.count()
        }});
//http://localhost:5000/api/OrderItems?$apply=groupby((OrderId,Order/Status),aggregate(Price with average as avg,Product with countdistinct as dcnt,Price with max as max,Order/Status with max as max_status,Price with min as min,Price with sum as sum,$count as cnt))

context.Customers.key({ Country: 'RU', Id: 1 });
//http://localhost:5000/api/Customers(Country='RU',Id=1)

context.OrderItems.key(1, i => i.Order.Customer);
//http://localhost:5000/api/OrderItems(1)/Order/Customer

context.OrderItems
    .select(i => {
        return {
            product: i.Product,
            Total: i.Count * i.Price,
            SumId: i.Id + i.OrderId
        }
    });
//http://localhost:5000/api/OrderItems?$select=Product&$compute=Count mul Price as Total,Id add OrderId as SumId

context.Orders.filter(o => o.Items.every(i => i.Price >= 2.1));
//http://localhost:5000/api/Orders?$filter=Items/all(d:d/Price ge 2.1)

context.Orders.filter(o => o.Items.some(i => i.Count > 2));
//http://localhost:5000/api/Orders?$filter=Items/any(d:d/Count gt 2)

let items = [1.1, 1.2, 1.3];
context.OrderItems.filter(i => items.includes(i.Price), { items: items });
//http://localhost:5000/api/OrderItems?$filter=Price in (1.1,1.2,1.3)

context.Orders.count();
//http://localhost:5000/api/Orders/$count

asEntitySet

context.Orders(o => o.AltCustomer).thenSelect(o => {{
    p1: o.Address,
    : o.Country,
    : o.Id,
    : o.Name,
    : o.Sex
}}).asEntitySet().orderby(o => o.Id)
//http://localhost:5000/api/Orders?$expand=AltCustomer($select=Address,Country,Id,Name,Sex)&$orderby=Id

GitHub.

, select, expand, groupby — — , , then: thenFilter, thenExpand, thenOrderby, thenOrderbyDescending, thenSkip, thenTop. select thenSelect , , , asEntitySet.

— filter/thenFilter, — select/thenSelect, — groupby , .

let count: number | null = null;
context.OrderItems.filter(i => i.Count == count, { count: count }); //http://localhost:5000/api/OrderItems?$filter=Count eq null

let s = {
    altCustomerId: 3,
    customerId: 4,
    dateYear: 2016,
    dateMonth: 11,
    dateDay: 20,
    date: null,
    name: 'unknown',
    status: "OdataToEntity.Test.Model.OrderStatus'Unknown'",
    count1: 0,
    count2: null,
    price1: 0,
    price2: null,
    product1: 'unknown',
    product2: 'null',
    orderId: -1,
    id: 1
};
context.Orders.filter(o => o.AltCustomerId == s.altCustomerId &&
    o.CustomerId == s.customerId &&
    (o.Date.getFullYear() == s.dateYear &&
        o.Date.getMonth() > s.dateMonth &&
        o.Date.getDay() < s.dateDay ||
        o.Date == s.date) &&
    o.Name.includes(s.name) &&
    o.Status == s.status, s)
    .expand(o => o.Items)
        .thenFilter(i => (i.Count == s.count1 ||
                i.Count == s.count2) &&
            (i.Price == s.price1 ||
                i.Price == s.price2) &&
            (i.Product.includes(s.product1) ||
                i.Product.includes(s.product2)) &&
            i.OrderId > s.orderId &&
            i.Id != s.id, s);
/*http://localhost:5000/api/Orders?$filter=AltCustomerId eq 3 and
    CustomerId eq 4 and
    (year(Date) eq 2016 and
        month(Date) gt 11 and
        day(Date) lt 20 or
        Date eq null) and
    contains(Name,'unknown') and
    Status eq OdataToEntity.Test.Model.OrderStatus'Unknown'&
    $expand=Items(
        $filter=(Count eq 0 or
            Count eq null) and
        (Price eq 0 or
            Price eq null) and
        (contains(Product,'unknown') or
            contains(Product,'null')) and
        OrderId gt -1 and
        Id ne 1)*/

JavaScript	OData
Math.ceil	ceiling
concat	concat
includes	contains
getDay	day
endsWith	endswith
Math.floor	floor
getHours	hour
indexOf	indexof
stringLength	length
getMinutes	minute
getMonth	month
Math.round	round
getSeconds	second
startsWith	startswith
substring	substring
toLowerCase	tolower
toUpperCase	toupper
trim	trim
getFullYear	year

OdataFunctions.stringLength

context.Customers.filter(c => OdataFunctions.stringLength(c.Name) == 5); //http://localhost:5000/api/Customers?$filter=length(Name) eq 5

OdataFunctions.arrayLength

context.Orders.filter(o => OdataFunctions.arrayLength(o.Items) > 2); //http://localhost:5000/api/Customers?$filter=Items/$count gt 2

select, filter getQueryUrl toArrayAsync.
getQueryUrl URL . TypeScript:

let url: URL = context.Customers
    .expand(c => c.AltOrders).thenExpand(o => o.Items).thenOrderby(i => i.Price)
    .expand(c => c.AltOrders).thenExpand(o => o.ShippingAddresses).thenOrderby(s => s.Id)
    .expand(c => c.Orders).thenExpand(o => o.Items).thenOrderby(i => i.Price)
    .expand(c => c.Orders).thenExpand(o => o.ShippingAddresses).thenOrderby(s => s.Id)
    .orderby(c => c.Country).orderby(c => c.Id).getQueryUrl();

OData :

http://localhost:5000/api/Customers?$expand=
AltOrders($expand=Items($orderby=Price),ShippingAddresses($orderby=Id)),
Orders($expand=Items($orderby=Price),ShippingAddresses($orderby=Id))
&$orderby=Country,Id

toArrayAsync Json. TypeScript:

context.Customers
    .expand(c => c.Orders).thenSelect(o => { return { Date: o.Date } }).orderby(o => o.Date)
    .asEntitySet().select(c => { return { Name: c.Name } }).orderby(c => c.Name).toArrayAsync();

Json:

[{
        "Name": "Ivan",
        "Orders": [{
                "Date": "2016-07-04T19:10:10.8237573+03:00"
            }, {
                "Date": "2020-02-20T20:20:20.000002+03:00"
            }
        ]
    }, {
        "Name": "Natasha",
        "Orders": [{
                "Date": "2016-07-04T19:10:11+03:00"
            }
        ]
    }, {
        "Name": "Sasha",
        "Orders": []
    }, {
        "Name": "Unknown",
        "Orders": [{
                "Date": null
            }
        ]
    }
]

, , toArrayAsync OdataParser:

import { OdataParser } from 'ts2odata';
import schema from './schema.json';

let odataParser = new OdataParser(schema);
context.Orders.toArrayAsync(odataParser);

(enum)

OData (Namespace), :

let context: OrderContext = OdataContext.create(OrderContext, 'http://localhost:5000/api', 'OdataToEntity.Test.Model');

在某些情况下，可能必须创建OdataParser对象以正确转换枚举。

import { OdataParser } from 'ts2odata';
import schema from './schema.json';

let odataParser = new OdataParser(schema);
let context: OrderContext = OdataContext.create(OrderContext, 'http://localhost:5000/api', 'OdataToEntity.Test.Model', odataParser);

资源

源代码在GitHub上。
在源文件夹中-程序包的节点代码，在测试文件夹中-测试。

希望我的TsToOdata项目对您有用，并使您免于编写单调代码的例程。

TypeScript中的类型化OData查询

安装TsToOdata

创建数据模型

(enum)

资源

More articles: