Npgsql is the .NET data provider for Postgresql. It allows any program developed for .NET framework to access a Postgresql database server. It is implemented in 100% C# code. Postgresql versions since 9.0 are officially supported, others may work.
sorry for that @roji . we implemented search functionality using EF.Functions.TrigramsAreSimilar and it's looking good. now we are trying to add unit test case for that search. er are getting below error message while running unit test method. if any one already face this issue. please share , how to fix this issue.
C# Implementation.
where(x=>EF.Functions.TrigramsAreSimilar(x.FirstName, searchText))
Error Message:
"The 'TrigramsAreSimilar' method is not supported because the query has switched to client-evaluation. This usually happens when the arguments to the method cannot be translated to server. Rewrite the query to avoid client evaluation of arguments so that method can be translated to server."
sample code:
Unit test case:
[Fact]
public void test()
{
imdbContext context = GetDBContect();
Rating rating = new Rating();
rating.AverageRating = 90;
rating.NumVotes = 90;
rating.Tconst = "TC891549";
context.Add(rating);
rating = new Rating();
rating.AverageRating = 90;
rating.NumVotes = 90;
rating.Tconst = "TC15491";
context.Add(rating);
rating = new Rating();
rating.AverageRating = 90;
rating.NumVotes = 90;
rating.Tconst = "TC15492";
context.Add(rating);
context.SaveChanges();
IMDBProvider iMDBProvider = new IMDBProvider(context);
IOrderedQueryable<Rating> ratings = iMDBProvider.GetRatings();
int ratingCount = ratings.Count();
}
private imdbContext GetDBContect()
{
DbContextOptions<imdbContext> options;
var builder = new DbContextOptionsBuilder<imdbContext>();
builder.UseInMemoryDatabase("DBTestDataBase");
options = builder.Options;
imdbContext imdbContext = new imdbContext(options);
imdbContext.Database.EnsureDeleted();
imdbContext.Database.EnsureCreated();
return imdbContext;
}Provide code to search context:
public IOrderedQueryable<Rating> GetRatings()
{
string sortedBy = "AverageRating";
return _context.Ratings
.Where(x => EF.Functions.TrigramsAreSimilar(x.Tconst, "1549") ||
EF.Functions.TrigramsAreSimilar(x.NumVotes.ToString(), "1549"))
.OrderBy(p => EF.Property<object>(p, sortedBy));
}Error Message:
"The 'TrigramsAreSimilar' method is not supported because the query has switched to client-evaluation. This usually happens when the arguments to the method cannot be translated to server. Rewrite the query to avoid client evaluation of arguments so that method can be translated to server."
I have a bit of an issue with using HashSet<T> properties.
I have a model like
class Thing {
public HashSet<long> Foos { get; init; }
}configured with
builder.Property(cr => cr.Foos)
.HasConversion(
v => new List<long>(v),
v => new HashSet<long>(v)
);because Npg doesn't support HashSet<T> directly.
Problem is, doing .Add() or .Remove() on that property, and then SaveChangesAsync()ing them, doesn't actually update anything in the database
class Thing
{
public List<long> Foos { get; set; }
[NotMapped]
public HashSet<long> FoosProxy {
get => new HashSet<long>(Foos);
set => Foos = new List<long>(value);
}
}
We are storing FirstName and lastName separate columns in the Customer Table. We are using Trigram search to filter FirstName and lastName. They have separate indexes for FirstName and LastName. also using order by FirstName and lastName.
We are planning to combine FirstName and LastName into a FullName single column and create index, search, order by. Which option is better: Single column or multiple column ? Please share your thoughts. thanks in advance.
Hello - I am looking for a way to modify PostGreSql queries in C#.
Examples:
Select from Table 1 => Select from Table 1 where col2='value2'
Select from Table 1 where col1='value' => Select from Table 1 where col1='value' and col2='value2'
In order to accomplish this I need to be able to track locations for where clauses, from clauses etc. in complex queries. I can use a SqlParser for this but cannot find something for C#.
I want to use this class but it has been declared as internal.
https://github.com/npgsql/npgsql/blob/0d8dd34ae957e26c9e61edaa35219353b3b0bd8e/src/Npgsql/SqlQueryParser.cs#L51
The use case has been documented in the wiki here but there does not seem to be an existing parser exposed for npgsql.
https://wiki.postgresql.org/wiki/Query_Parsing
Anyone have any ideas on how to accomplish this?
