How do I produce a list of records in a view based on values in another table

Question

0.00/5 (No votes)

See more:

I have a view that produces Vendors that are attached to parts. These parts have Letters at the beginning of them. I have a table that has values for the letters. So when selected it shows a list of vendors and their prices. I do not have a Selling price in the table and that is where this other table comes in. Based on the letter it begins with the Average cost should be multiplied by the value in this table. The table that i will refer to as ProductMaster has the VendorId, MaterialId, LastCost, AverageCost, and StandardCost. The table referred to as BillingSettings has a list of records with a type and the value to be used. ProductMaster Also has reference to Vendors, Parts, and BillingSettings. I have tried a few things but have not been successful in getting this to work. It seems like each route i take there is something in the code that it doesnt like. All of these are of decimal Type and the Values to be multiplied are of Type Money. In MSSQL. Below is what i have currently and has an issue with the select statement.

What I have tried:

C#

public ActionResult Index(Guid id)
{
    var dict = db.BillingSettings.Where(t => t.Types == 1).Select(x => new { x.StartingValues, x.Value });

    var productMaster = db.ProductMaster.Include(p => p.Parts).Include(p => p.Vendor).Include(p => p.BillSettings).Where(m => m.Material == id).Select(n =>
    {
        n.SellingPrice = n.AverageCost * (dict.FirstOrDefault(x => n.Parts.PartNumber.StartsWith(x.StartingValues))?.Value ?? 1);

    });

    return View(productMaster.ToList());
}

Posted 29-Feb-20 9:14am

Member 13812021

Updated 3-Mar-20 3:55am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2020-03-03T03:56:00

Select produces a projection - it takes each element in the input sequence, passes it into a function, and returns a sequence of the values returned from the function.

You are trying to pass in an action which doesn't return a value. That won't work.

Assuming your ProductMaster entity already has a SellingPrice property, you could try adding return n; to your lambda. However, Entity Framework won't be able to convert that to a SQL query, so you'll need to add .AsEnumerable() to run the projection in memory:

C#

... .Where(m => m.Material == id).AsEnumerable().Select(n =>
{
    n.SellingPrice = ...;
    return n;
});