Mongodb C# - How to get max date - ( Aggregate )
The documents look like this:
{
ContractNumer: 10,
SomeField: "ABC",
ValueContract: 17.7,
DataProcessing: '2021-01-19 10:23:20:10',
Status: 1
}
With C# mongodb driver, how do you write something like this?
Select ContractNumer, SomeField, ValueContract, DataProcessing, Status
FROM TAB T1
INNER JOIN
(SELCT ContractNumer, MAX(DataProcessing) AS MAX_DATE FROM TAB) SUB_T
ON T1.ContractNumer = SUB_T.ContractNumer
AND T1.DataProcessing = SUB_T.MAX_DATE
WHERE ....
So that for each combination (group) of ContractNumber we'll get max of DataProcessing
1 answer
See also questions close to this topic
-
Convert CSV file data from any language to English in C#
I would like to convert CSV file data from multi languages such as Spanish, Russian, European etc to English language in C# program.
Convert all characters like Ó, É to English characters.
Thanks.
-
I get an error when solving this problem, How can I fix?
I am trying to solve the Climbstairs problem but in reverse, where I want to know the number of steps I have to take to go down.
I can go down 1, 2, 3 or 4 steps at the same time. That is, if I am at step i, I can go down to step i - a for any of the values 1, 2, 3 or 4 of a.
I have the following code but I don't know what happens:
I got this error: System.IndexOutOfRangeException in this line:
steps[i] += steps[i - a];Why I have this error?
public static int DownStairs(int n) { int[] steps = new int[n + 1]; steps[n] = 1; steps[n - 1] = 1; for (int i = n-2; i>=0; i--) { for(int a = 1; a<=4; a++) { steps[i] += steps[i - a]; } } return steps[n]; } static void Main(string[] args) { int n = 5; DownStairs(n); } -
How to delete multiple blank lines in a WPF DataGrid imported from an Excel file
I have a WPF DataGrid which I fill with imported data from an Excel file (*. Xlsx) through a class, the problem is that multiple blank lines are added to the end of the DataGrid that I don't see how to delete. I attach my code.
<DataGrid Name="dgvMuros" Height="210" Margin="8" VerticalAlignment="Top" Padding="5,6" ColumnWidth="50" IsReadOnly="False" AlternatingRowBackground="Azure" GridLinesVisibility="All" HeadersVisibility="Column" Loaded="dgvMuros_Loaded" CellEditEnding="DataGrid_CellEditEnding" ItemsSource="{Binding Data}" HorizontalGridLinesBrush="LightGray" VerticalGridLinesBrush="LightGray" > </DataGrid>With this method I import the data from the Excel file.
public void ImportarMuros() { ExcelData dataFronExcel = new ExcelData(); this.dgvMuros.DataContext = dataFronExcel; txtTotMuros.Text = dataFronExcel.numMuros.ToString(); cmdAgregarMuros.IsEnabled = false; cmdBorrarMuros.IsEnabled = false; cmdImportar.IsEnabled = false; } public class ExcelData { public int numMuros { get; set; } public DataView Data { get { Excel.Application excelApp = new Excel.Application(); Excel.Workbook workbook; Excel.Worksheet worksheet; Excel.Range range; workbook = excelApp.Workbooks.Open(Environment.CurrentDirectory + "\\MurosEjemplo.xlsx"); worksheet = (Excel.Worksheet)workbook.Sheets["DatMuros"]; int column = 0; int row = 0; range = worksheet.UsedRange; DataTable dt = new DataTable(); dt.Columns.Add("Muro"); dt.Columns.Add("Long"); dt.Columns.Add("Esp"); dt.Columns.Add("X(m)"); dt.Columns.Add("Y(m)"); dt.Columns.Add("Dir"); for (row = 2; row < range.Rows.Count; row++) { DataRow dr = dt.NewRow(); for (column = 1; column <= range.Columns.Count; column++) { dr[column - 1] = Convert.ToString((range.Cells[row, column] as Excel.Range).Value); } dt.Rows.Add(dr); dt.AcceptChanges(); numMuros = dt.Rows.Count; } workbook.Close(true, Missing.Value, Missing.Value); excelApp.Quit(); return dt.DefaultView; } } } -
Mongodb aggregation : get task overdue count by user
Trying to get the count of tasks based on various statuses & count of overdue items for each user. Below is the sample document:
[{ _id:"12324332432", caseInd:"TASK123", TaskId:"12345", dueDate:ISODate("2021-03-22T00:00:00Z"), assingee:"ABC", status:"IN_PROGRESS", completionDate:null }, { _id:"12324332433", caseInd:"TASK124", TaskId:"12345", dueDate:ISODate("2021-03-01T00:00:00Z"), assingee:"CDE", status:"IN_PROGRESS", completionDate:null }, { _id:"12324332434", caseInd:"TASK125", TaskId:"12345", dueDate:ISODate("2021-03-31T00:00:00Z"), assingee:"ABC", status:"COMPLETED", completionDate:ISODate("2021-03-01T00:00:00Z") }...]Using aggregation , I want to get the count of tasks (completed ,InProgress & overdue) for each user. like
[{ _id : ABC , data: [ {status:"Completed", count:15}, {status:"InProgress", count:3}, {status:"Overdue", count:3}] },{ _id : CDE, data: [ {status:"Completed", count:5}, {status:"InProgress", count:1}, {status:"Overdue", count:0}] }]I have tired this solution, but not sure how to include overdue count.
db.colName.aggregate([ {$project: {_id:0, dueDateIn:{$divide:[{$subtract:[new Date(),$dueDate]},86400000]} } }, { $group:{ _id:{"assignee":"$assignee", "status":"$status"}, count:{$sum:1} }, $group:{ _id:"$_id.assignee" , result:{$push:{"status":"$_id.status", count:"$count"}} } }])``` > Any help would be highly appreciated. Thank you. -
mongodb $group aggregation $last takes long time
I wish to get the last entries by unique car_id (last entries = car_timestamp). So my expected result should be the first 3 cars (assuming there are only 3 cars in the 5 millions entries).
for this, I'm using the pipeline with $group where:
{ '$group': { '_id': '$car_id', 'car_timestamp': { '$last': '$car_timestamp' }, "source_a":{ '$last': "$source_a" }, "source_b":{ '$last': "$source_b" }, "source_c":{ '$last': "$source_c" }, } },While the desired result are correct the execution time is above 30 seconds. The collection is indexed by car_id and car_timestamp. To improve it, I added to the pipeline:
{'$match' : { '$and': [ {'car_timestamp' : {'$gte':date_from}}, //date_from = in the past 30 days ] } },but it still takes some time to execute. Is this reasonable or am I doing something wrong? Are there any other alternatives for this?
the json example (there are millions of records like this):
[{ "_id" : ObjectId("603a749632f077a3b2b5d51c"), "car_id" : 7087, "car_timestamp" : ISODate("2018-10-01T00:04:41.000Z"), "source_a" : "TS", "source_b" : "TW", "source_c" : "TW", "destination" : "Woyoming", "eta" : "2018-06-06", "country_req" : "USA", }, { "_id" : ObjectId("603a749632f077a3b2b5d51d"), "car_id" : 7088, "car_timestamp" : ISODate("2018-10-01T00:03:41.000Z"), "source_a" : "TS", "source_b" : "TW", "source_c" : "TW", "destination" : "New York", "eta" : "2018-06-06", "country_req" : "Japan", }, { "_id" : ObjectId("603a749632f077a3b2b5d51e"), "car_id" : 7089, "car_timestamp" : ISODate("2018-10-01T00:12:11.000Z"), "source_a" : "TS", "source_b" : "TW", "source_c" : "TW", "destination" : "Florida", "eta" : "2018-06-06", "country_req" : "China", }, { "_id" : ObjectId("603a749632f077a3b2b5d51f"), "car_id" : 7087, "car_timestamp" : ISODate("2018-09-20T01:12:33.000Z"), "source_a" : "TS", "source_b" : "TW", "source_c" : "TW", "destination" : "Florida", "eta" : "2018-06-06", "country_req" : "Finland", }, { "_id" : ObjectId("603a749632f077a3b2b5d520"), "car_id" : 7088, "car_timestamp" : ISODate("2018-09-20T11:13:14.000Z"), "source_a" : "TS", "source_b" : "TW", "source_c" : "TW", "destination" : "Ohio", "eta" : "2018-06-06", "country_req" : "Finland", } ] -
How to match two values in MongoDB collection query?
Here is my json code
"_id" : ObjectId("6018430823544f4550724308"), "ownerId" : ObjectId("5fd471605dfe592674888e3b"), "screen" : { "ScreenName" : "Aud1", "Seats" : "200" }, "shows" : [ { "showid" : ObjectId("601a16763028260ce88428f1"), "MovieName" : "Joker", "Format" : "2D", "Language" : "English", "Date" : "1/3/2021", "ShowTime" : "2pm", "Vip" : "400", "Premium" : "300", "Executive" : "200", "Normal" : "100" }, { "showid" : ObjectId("601a16983028260ce88428f2"), "MovieName" : "Churuli", "Format" : "2D", "Language" : "Malayalam", "Date" : "02/2/2021", "ShowTime" : "5pm", "Vip" : "400", "Premium" : "300", "Executive" : "200", "Normal" : "100" }, { "showid" : ObjectId("603d08f1cdaa7a26f8e17ebd"), "MovieName" : "bigb", "Format" : "2D", "Language" : "Malayalam", "Date" : "1/3/2021", "ShowTime" : "10am", "Vip" : "400", "Premium" : "300", "Executive" : "200", "Normal" : "100" } ] }here i want to match
moviename==movienamewhich user giving anddate==datewhich user giving. I want to get the result if this two condition is trueeg:
moviename==joker and date==1/3/2021expecting result:
{ "showid" : ObjectId("601a16763028260ce88428f1"), "MovieName" : "Joker", "Format" : "2D", "Language" : "English", "Date" : "1/3/2021", "ShowTime" : "2pm", "Vip" : "400", "Premium" : "300", "Executive" : "200", "Normal" : "100" } -
Microsoft.AspNetCore.Authentication.JwtBearer.JwtBearerHandler[3\
When warn: Microsoft.AspNetCore.DataProtection.Repositories.FileSystemXmlRepository[60]
Storing keys in a directory '/root/.aspnet/DataProtection-Keys' that may not be persisted outside of the container. Protected data will be unavailable when container is destroyed.
warn: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[35]
No XML encryptor configured. Key {e43b756a-2818-4898-8730-5e8e0f230be7} may be persisted to storage in unencrypted form.
info: IdentityServer4.Startup[0]
Starting IdentityServer4 version 4.1.1+cebd52f5bc61bdefc262fd20739d4d087c6f961f
info: IdentityServer4.Startup[0]
You are using the in-memory version of the persisted grant store. This will store consent decisions, authorization codes, refresh and reference tokens in memory only. If you are using any of those features in production, you want to switch to a different store implementation.
info: IdentityServer4.Startup[0]
Using the default authentication scheme Identity.Application for IdentityServer
info: Microsoft.Hosting.Lifetime[0]
Now listening on: https://[::]:8081
info: Microsoft.Hosting.Lifetime[0]
Now listening on: http://[::]:8080
info: Microsoft.Hosting.Lifetime[0]
Application started. Press Ctrl+C to shut down.
info: Microsoft.Hosting.Lifetime[0]
Hosting environment: Production
info: Microsoft.Hosting.Lifetime[0]
Content root path: /app
info: IdentityServer4.Hosting.IdentityServerMiddleware[0]
Invoking IdentityServer endpoint: IdentityServer4.Endpoints.TokenEndpoint for /connect/token
info: IdentityServer4.AspNetIdentity.ResourceOwnerPasswordValidator[0]
Credentials validated for username: muthu
info: IdentityServer4.Validation.TokenRequestValidator[0]
Token request validation success, {
"ClientId": "sdgfsdsgsdg",
"ClientName": "Swagger UI",
"GrantType": "password",
"Scopes": "api",
"AuthorizationCode": "********",
"RefreshToken": "********",
"UserName": "sample",
"Raw": {
"grant_type": "password",
"username": "sample",
"password": "REDACTED"
}
}
fail: Microsoft.AspNetCore.Authentication.JwtBearer.JwtBearerHandler[3]
Exception occurred while processing message.
System.InvalidOperationException: IDX20803: Unable to obtain configuration from: 'http://*:8080/.well-known/openid-configuration'.
---> System.IO.IOException: IDX20804: Unable to retrieve document from: 'http://*:8080/.well-known/openid-configuration'.
---> System.UriFormatException: Invalid URI: The hostname could not be parsed.
at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)
at System.Uri..ctor(String uriString, UriKind uriKind)
at Microsoft.IdentityModel.Protocols.HttpDocumentRetriever.GetDocumentAsync(String address, CancellationToken cancel)
--- End of inner exception stack trace ---
at Microsoft.IdentityModel.Protocols.HttpDocumentRetriever.GetDocumentAsync(String address, CancellationToken cancel)
at Microsoft.IdentityModel.Protocols.OpenIdConnect.OpenIdConnectConfigurationRetriever.GetAsync(String address, IDocumentRetriever retriever, CancellationToken cancel)
at Microsoft.IdentityModel.Protocols.ConfigurationManager`1.GetConfigurationAsync(CancellationToken cancel)
--- End of inner exception stack trace ---
at Microsoft.IdentityModel.Protocols.ConfigurationManager`1.GetConfigurationAsync(CancellationToken cancel)
at Microsoft.AspNetCore.Authentication.JwtBearer.JwtBearerHandler.HandleAuthenticateAsync()
fail: IdentityServer4.AccessTokenValidation.IdentityServerAuthenticationHandler[0]
IDX20803: Unable to obtain configuration from: 'http://*:8080/.well-known/openid-configuration'.
System.InvalidOperationException: IDX20803: Unable to obtain configuration from: 'http://*:8080/.well-known/openid-configuration'.
---> System.IO.IOException: IDX20804: Unable to retrieve document from: 'http://*:8080/.well-known/openid-configuration'.
---> System.UriFormatException: Invalid URI: The hostname could not be parsed.
at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)
at System.Uri..ctor(String uriString, UriKind uriKind)
at Microsoft.IdentityModel.Protocols.HttpDocumentRetriever.GetDocumentAsync(String address, CancellationToken cancel)
--- End of inner exception stack trace ---
at Microsoft.IdentityModel.Protocols.HttpDocumentRetriever.GetDocumentAsync(String address, CancellationToken cancel)
at Microsoft.IdentityModel.Protocols.OpenIdConnect.OpenIdConnectConfigurationRetriever.GetAsync(String address, IDocumentRetriever retriever, CancellationToken cancel)
at Microsoft.IdentityModel.Protocols.ConfigurationManager`1.GetConfigurationAsync(CancellationToken cancel)
--- End of inner exception stack trace ---
at Microsoft.IdentityModel.Protocols.ConfigurationManager`1.GetConfigurationAsync(CancellationToken cancel)
at Microsoft.AspNetCore.Authentication.JwtBearer.JwtBearerHandler.HandleAuthenticateAsync()
at Microsoft.AspNetCore.Authentication.JwtBearer.JwtBearerHandler.HandleAuthenticateAsync()
at Microsoft.AspNetCore.Authentication.AuthenticationHandler`1.AuthenticateAsync()
at Microsoft.AspNetCore.Authentication.AuthenticationService.AuthenticateAsync(HttpContext context, String scheme)
at IdentityServer4.AccessTokenValidation.IdentityServerAuthenticationHandler.HandleAuthenticateAsync()
info: IdentityServer4.AccessTokenValidation.IdentityServerAuthenticationHandler[7]
Bearer was not authenticated. Failure message: IDX20803: Unable to obtain configuration from: 'http://*:8080/.well-known/openid-configuration'.
info: IdentityServer4.AccessTokenValidation.IdentityServerAuthenticationHandler[12]
AuthenticationScheme: Bearer was challenged.
-
How to display data in a reusable Table component in Blazor
I'm trying to create a reusable MasterTable component in Blazor.
So far, I've defined the MasterTable as
@using AntDesign @using MyNamespace.Blazor.ViewModels @typeparam TItem <Table TItem="TItem" DataSource="@Data"> @{ foreach (var col in Columns) { <Column Title="@col.Label" @bind-Field="@col.Key" /> } } </Table> @code { private List<TItem> _data; [Parameter] public List<TItem> Data { get => _data; set => _data = value ?? new List<TItem>(); } [Parameter] public TableColumnViewModel[] Columns { get; set; } }where TableColumnViewModel is defined simply as
public class TableColumnViewModel { public string Key { get; set; } public string Label { get; set; } }I would like to create an instance of the MasterTable in a page for Daily Tasks but so far I'm only able to get it to display like this:
My attempt to implement MasterTable is as follows:
@page "/Tasks/Daily"; @using MyNamespace.Blazor.Services; @using MyNamespace.Blazor.ViewModels; @using MyNamespace.Api.Client.Model; @inject ITasksService _tasksService; <h1>Daily Tasks</h1> <MasterTable TItem="TaskStatus" Data="_tasks" Columns="cols"> </MasterTable> @code { private List<TaskStatus> _tasks = new List<TaskStatus>(); protected override async Task OnInitializedAsync() { _tasks = await _tasksService.GetTaskStatusAsync(); } TableColumnViewModel[] cols = { new TableColumnViewModel { Key = "id", Label = "ID" }, new TableColumnViewModel { Key = "description", Label = "ID" }, new c { Key = "type", Label = "Type" } }; }With TaskStatus defined as
public class TaskStatus { public TaskStatus(int taskStatusId = default(int), string statusDescription = default(string)) { this.TaskStatusId = taskStatusId; this.StatusDescription = statusDescription; } public int TaskStatusId { get; set; } public string StatusDescription { get; set; } }What do I need to do to get the MasterTable template to display the list of TaskStatus objects instead of the keys from TableColumnViewModel?
To be clear - instead of just using the component without wrapping it, the issue is that I want to isolate the CSS in the context of the 3rd party component, so that only the necessary CSS is loaded.
-
ASP.NET using return RedirectToAction(" ", " ") how to use this to redirect to a cshtml
I can not find a useable tutorial that explains how to redirect using REdirectToAction. can someone share a link that that explains ALL the steps needed to use this? I think I am having a hard time understanding how the parameters are given can find an HTML file with a model in the parameter? or is it a controller? I am very lost on how they communicate. please someone if you can help.
-
MongoDB: FieldPath field names may not contain ‘.’
I'm trying to do a
$lookupfroma collection whose documents look this way:{ "_id": {"$oid":"5f1957c7cdf25116937ed3ef"}, "idSensor": {"$numberLong":"3"}, ... "data": { "inicio":"2019-11-28T16:09:08+01:00", "fin":"2019-11-28T16:09:18+01:00", ... }, ... }I deliver them to the
$unwindstage justas"array", and then I try a$match:[ ... { "$match": { "$expr": { "$and": [ {"array.idSensor": 3}, {"$gt": ["array.data.inicio", "ISODate('2021-02-28T23:59:59+01:00')"]}, {"$lt": ["array.data.fin", "ISODate('2021-03-15T00:00:01+01:00')"]} ] } } }, ... ]Here's where I'm getting an error message:
"FieldPath field names may not contain '.'."
Filtering just by
"array.idSensor"is OK, I checked it, so the problem lies within the dates.Thanks in advance for your help.
-
How can i create object from 2 arrays in mongodb aggregation?
I have following two collections with their respective fields:
blogCollection : _id, blogId, description, date images: _id, blogId, imageUrl, typeThe data of images collection are like this
"images": [ { "_id": "someid", "blogId": "blog2912201715021", "imageUrl":"https://someurl.com", "type":"Food" }, { "_id": "someid", "blogId": "blog2912201782222", "imageUrl":"https://someurl.com", "type":"Adventure" }, { "_id": "someid", "blogId": "blog2181881291131", "imageUrl":"https://someurl.com", "type":"Food" }, .........I have following query
db.blogCollections.aggregate([ { $match: { blogId: {'some_given_id'}, }, }, { $lookup: { from: 'images', let: { bId: '$blogId' }, pipeline: [ { $match: { $expr: { $or: [{ $eq: ['$blogId', '$$bId'] }], }, }, }, { $project: { _id: 0, imageUrl: 1 ,type:1} }, ], as: 'image', }, }, { $set: { // i want to add a new field called allImages of certain type in blog as array of imageUrls // something like this [imageUrl1,imageUrl2,.........] // allImages: , }, }, ]);is there a way that i can use condition in set? Suppose i want to add images to type 'Food', then i should store only the image urls of food type images. Is it possible? I tried everything filter, cond and all but it doesn't work :/ ... Is there a way ?
-
MongoDB: How to map faceted results into a single object of keys and values
I'm trying to build a query which will give me an idea of what equipment people will require at a given allocated time. I do this by taking a set of teams, and summing the list of equipment, at any given time to determine how much equipment is in use.
I have a set of things which look like this:
const teams = [ { id: 'a', start: date('2020-04-30-10T17:00:00.000Z'), end: date('2020-05-01T05:00:00.000Z'), equipment: [ { id: 'balls', quantity: 2 }, { id: 'bats', quantity: 4 } ] }, { id: 'b', start: date('2020-04-30-10T20:00:00.000Z'), end: date('2020-05-01T08:00:00.000Z'), equipment: [ { id: 'balls', quantity: 2 }, { id: 'bats', quantity: 4 } ] } ]Then I have a query which I build by looping over an array of times-spans and building facets from them, so the facet keys are dynamic:
const { facet, project } = occurrences .reduce(({ facet, project }, { key, fromDate, toDate }) => { facet[key] = [ { $match: { start: { $not: { $gt: toDate } }, end: { $not: { $lt: fromDate } } } }, { $unwind: '$equipment' }, { $group: { _id: '$equipment.id', quantity: { $sum: '$equipment.quantity' } } }, ] return facet }, {}) const equipmentTotals = await db .collection('teams') .aggregate([ { $facet: facet } ]) .toArray()This results in output as follows:
{ "202004301700": [ { "_id": "bats", "quantity": 2 }, { "_id": "balls", "quantity": 4 } ], "202004302000": [ { "_id": "bats", "quantity": 2 }, { "_id": "balls", "quantity": 4 } ], "202004302300": [ { "_id": "bats", "quantity": 2 }, { "_id": "balls", "quantity": 4 } ] }What I would want the result to look like, is:
{ "202004301700": [ equipment: { balls: 2, bats: 4 } ], "202004302000": [ equipment: { balls: 2, bats: 4 } ], "202004302300": [ equipment: { balls: 2, bats: 4 } ] }Or any reasonable equivalent, where the list of equipment totals is an object containing balls and bats, rather than an array of each equipment type.
I've tried tens of solutions from docs, stackoverflow, and other places which involve $map, $reduce, $arrayToObject, and a variety of other things. To no avail.
This must be possible. What am I missing?
-
MongoDB Fluent Aggregate Conditional Stages
When using the Fluent aggregation interface, why doesn't the stages added later not work (stage 3)?
var fluentPipeline = _userToGroup.Aggregate() .AppendStage<BsonDocument>("stage 1") .AppendStage<BsonDocument>("stage 2"); if (condition) fluentPipeline.AppendStage<BsonDocument>("stage 3"); fluentPipeline.ToListAsync();Stage 3 works when added to the pipeline in the same code line like below. Which means it is not a problem with the stage but how the stage is added to the pipeline as I understand it. The question is why?
var fluentPipeline = _userToGroup.Aggregate() .AppendStage<BsonDocument>("stage 1") .AppendStage<BsonDocument>("stage 2") .AppendStage<BsonDocument>("stage 3"); -
E11000 duplicate key error collection index: _id_ in mongo driver
I am using
InsertManymethod in my service to insert 1000 documents in to MongoDB(in AWS DocumentDB). It is working fine when I use one pod in AWS. But If I scale my service to 2 or more pods it throwsE11000 duplicate key error collection: myDB index: _id_. Can anybody help to resolve this issue? -
How to insert (push) a new object into a nested array using MongoDB C#?
{ "_id" : "", "Username" : "", "Points" : 0, "CompletedAchievements" : [], "ActiveAchievements" : [], "Kudos" : [], "Teams" : [], "UserType" : "Standard" }Here is my record I'd like to insert into. I am trying to add the following object into the ActiveAchievements array.
{ "_id": "my new id", "progress": 10 }I wish to add this into the Active Achievements array which already exists on a record(which has a unique ID I can get it by) in my database. As well as more in the future.
How would I do this using the MongoDb.Driver? In C#