How do I enable access and linq querying of entity framework DbSet?

[kkulick]

How do I enable linq querying of entity framework DbSet?

I'm trying to use ClearScriptV8 to allow custom scripts to be created which will be executed via ClearScriptV8 in an ASP .Net Core Web API with Entity Framework. I was able to register the database context and can access it in the script however, if I try to write a linq query or use the .Where() or .Select(), it just tells me 'Where' and 'Select' are not found on object 'xyz' where xyz is the entity model. Is this sort of thing supported in ClearTextV8?

[ClearScriptLib]

Hi @kkulick,

You can use Linq from script code, but there are caveats. Let's go through a simple example with DbSet.

First, the basics:

public class Product {
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}
public class ProductContext : DbContext {
    public DbSet<Product> Products { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=ProductDb;Trusted_Connection=True;");
    }
}

Now, let's set up the database and script engine:

using var context = new ProductContext();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

var products = context.Products;
products.Add(new Product { Name = "Laptop", Price = 999.99M });
products.Add(new Product { Name = "Desktop", Price = 1499.99M });
products.Add(new Product { Name = "Server", Price = 2999.99M });
context.SaveChanges();

using var engine = new V8ScriptEngine();
engine.AddHostType(typeof(Console));
engine.AddHostType(typeof(Enumerable));
engine.Script.products = products;

At this point, all Enumerable Linq extension methods are callable from script code. However, calling a method like Where is a bit clumsy because of its strongly typed predicate parameter. JavaScript functions aren't strongly typed in the .NET sense, so a bit of work is required:

engine.AddHostType(typeof(Func<,>));
engine.AddHostType(typeof(Product));
engine.AddHostType(typeof(Boolean));
engine.Execute(@"
    for (let product of products.Where(new Func(Product, Boolean, p => p.Price < 1000))) {
        Console.WriteLine(`ID: ${product.ProductId}, Name: ${product.Name}, Price: ${product.Price}`);
    }
");

However, you can simplify things by providing a script-friendly version of Where:

public static class ScriptHelpers {
    public static IEnumerable<T> where<T>(this IEnumerable<T> source, dynamic predicate) {
        return source.Where(item => predicate(item));
    }
}

And now you can do this:

engine.AddHostType(typeof(ScriptHelpers));
engine.Execute(@"
    for (let product of products.where(p => p.Price < 1000)) {
        Console.WriteLine(`ID: ${product.ProductId}, Name: ${product.Name}, Price: ${product.Price}`);
    }
");

Good luck!

[kkulick]

That works great. Would you have a quick explanation of how to figure out what the correct syntax is when creating a helper function for the other Enumerable functions? Specifically, I need to create one for .Select() and probably others, but figuring out what the syntax should be seems elusive. I'd like to understand how to figure out what syntax to use so I don't have to bother the community with specific questions.

[ClearScriptLib]

I need to create one for .Select() and probably others, but figuring out what the syntax should be seems elusive.

Sure, here's a script-friendly select extension:

public static IEnumerable<object> select<T>(this IEnumerable<T> source, dynamic selector) {
    return source.Select((item, index) => selector(item, index));
}

And here's how you might use it:

// JavaScript
for (const name of products.select(product => product.Name)) {
    Console.WriteLine(name);
}
for (const { index, name } of products.select((product, index) => ({ index, name: product.Name }))) {
    Console.WriteLine(`Index: ${index}, Name: ${name}`);
}

Output:

Laptop
Desktop
Server
Index: 0, Name: Laptop
Index: 1, Name: Desktop
Index: 2, Name: Server

I'd like to understand how to figure out what syntax to use so I don't have to bother the community with specific questions.

Please don't hesitate to ask questions. This is a fairly advanced use case that combines two radically different languages and type systems in a way that's unique to ClearScript. We're happy to help!