Retrieving data from multiple tables using linq-to-sql

Question

We need to populate a bunch of controls on a form using Linq-to-SQL to retrieve the data. In order to get the data, we need to join several tables.

We have the following method:

First file:

public IEnumerable<object> getPRs()
{
    DataContext db = new DataContext();

    var prQuery = (from p in db.Invoice
                   join prd in db.InvoiceDetails on p.ID equals prd.ID
                   join pra in Accounting on p.ID equals pra.ID
                   where 1 == 1
                   orderby p.DownloadDate descending, p.PRNumber, Convert.ToInt32(p.Item)
                   select new
                          {
                              p.ID,
                              DownloadDate = Convert.ToString(p.DownloadDate),
                              p.PRNumber,
                              p.Item,
                              p.Material,
                              p.ClientMaterial,
                              p.Description,
                              p.Client,
                              p.Price,
                              p.UC,
                              prd.Supplier,
                              prd.Duns,
                              prd.RFQ,
                              prd.RFQDate,
                              prd.PO,
                              prd.PODate,
                              POValue = prd.Price * prd.Quantity,
                              pra.SO,
                              pra.SODate,
                              pra.SOValue,
                              pra.GR,
                              pra.GRDate,
                              pra.GI,
                              pra.GIDate,
                              pra.SInvoice,
                              pra.SInvoiceDate,
                              pra.CInvoice,
                              pra.CInvoiceDate,
                              p.isActive
                          }).ToList();
    return prQuery;
}

And we are calling the method like this on the second file:

IEnumerable<object> data = FirstFile.GetPRs();
PRNumberTextBox.Text = data.PRNumber;

The last line will give an error because we can't access the PRNumber member from the data object. In order to fill in all the text boxes, how can we call our function and add the necessary info?

IEnumerable<object> doesn't have PRNumber property.

Anatolii Gabuza
– Anatolii Gabuza

2014-10-21 13:26:34 +00:00
Commented Oct 21, 2014 at 13:26 — Anatolii Gabuza
– Anatolii Gabuza, Commented Oct 21, 2014 at 13:26

DavidG · Accepted Answer · 2014-10-21 13:47:41Z

2

You are returning an anonymous type. I would recommend you create a model class to return your data:

public class PRModel
{
    public int ID { get; set; }
    public string DownloadDate { get; set; }
    //etc... you can fill in the rest yourself
}

Then your query becomes something like this:

var prQuery = (from p in db.Invoice
               join prd in db.InvoiceDetails on p.ID equals prd.ID
               join pra in Accounting on p.ID equals pra.ID
               where 1 == 1
               orderby p.DownloadDate descending, p.PRNumber, Convert.ToInt32(p.Item)
               select new PRModel //<---This is the change here
               {
                   ID = p.ID,
                   DownloadDate = Convert.ToString(p.DownloadDate),
                   PRNumber = p.PRNumber,
                   //snipp
               }

And finally, your return type should now be IEnumerable<PRModel>. Also, as it's an enumeration (i.e. you may have 0, 1 or many items) you need to iterate through them:

IEnumerable<PRModel> data = FirstFile.GetPRs();
foreach(var pr in data)
{
    PRNumberTextBox.Text = pr.PRNumber;
}

Edit:

In this case, if you only want to return an individual invoice, it's even easier to do. Instead of doing this:

select new PRModel
{
    //lots of rows
}

You could simply do this:

select p

Which in turn makes your return type IEnumerable<Invoice>.

edited Oct 21, 2014 at 13:47

answered Oct 21, 2014 at 13:31

DavidG

120k13 gold badges232 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rocshy Over a year ago

And if we have a lot of table (i.e 50 tables) do we need to make a class for every table? Or do we need to create a class for every query?

DavidG Over a year ago

You already have a class for each table, your entity objects. You could return one of them if you like.

Anatolii Gabuza · Accepted Answer · 2014-10-21 13:50:05Z

Issue:
PRNumber is not a member of IEnumerable<object>. You need to iterate through IEnumerable to access each element.

Solution:
Second issue is that you're implicitly casting anonymous types from linq query to an object so every element in a list you get from getPRS() will be an object. You can't access properties without casting to a specific type (as per @DavidG solution - new class/type for every aggregate function) or defining type at a runtime using dynamic. The easiest fix will be to change return type to IEnumerable<dynamic>:

public IEnumerable<dynamic> GetList()
        {
            var list = new List<int> {1, 2, 3, 4};
            return (from i in list
                   select new {
                       Integer = i,
                       Str = "Str" + i
                   }).ToList();
        }

and then:

var items = GetList();
Console.WriteLine(items.First().Integer); // prints "1" to console

Collectives™ on Stack Overflow

Retrieving data from multiple tables using linq-to-sql

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related