LinQ query with multiple tables and extracting data

Question

I'm doing a query to inner join 4 tables and I have to extract data and convert into string and place it in an array for it.

    var query = from a in context.as
                            join b in context.bs on a.prikey equals b.forkey
                            join c in context.cs on b.prikey equals c.forkey
                            join d in context.ds on c.prikey equals d.forkey
                            where b.gender == gender
                            where c.age == age
                            select new
                            {
                                a.Name,
                                a.Age,
                                b.Gender,
                            };
string[] results = new string[] {}
return results;

Normally, if a single table is involved a as = plural of table a

as t = query.First() 
string[] results = new string[] {t.Name, t.Age, t.Gender}
return results;

I'm missing a step to extract the data.

Answer 1

It depends what exactly you want to do with the data. Your code won't actually compile at the moment as it's trying to create an anonymous type with multiple properties all called "arg" but I'm assuming you've really got a more sensible query.

Ultimately, the fact that you're using multiple tables is irrelevant here - you're only getting a single result element at a time: the fact that each result element contains data from multiple tables is neither here nor there in terms of how you access it.

Now I've just noticed that you say you want to "extract data and convert into string". If possible, you should express that in your query. You may be able to do that at the database, or you may need to force the final part of the execution to execute locally, like this:

// Not executed yet!
var dbQuery = from a in context.a
              join b in context.bs on a.prikey equals b.forkey
              join c in context.cs on b.prikey equals c.forkey
              join d in context.ds on c.prikey equals d.forkey
              where ...
              select { a.Age, b.Name, c.Salary, d.Location };

// This still won't talk to the database!
var finalQuery = dbQuery.AsEnumerable()
                        .Select(x => string.format("Age: {0}; Name: {1}; " +
                                                   "Salary: {2}; Location: {3}",
                                                   x.Age, x.Name, x.Salary,
                                                   x.Location));

// This will finally execute the query
string[] results = finalQuery.ToArray();

Now you don't have to do it like this - but it's probably the best approach, at least with the amount of information you've given us. If you can tell us more about how you're trying to combine the data from the multiple tables, we may be able to help you more.

EDIT: Okay, now you've given us a bit more information, I suspect you want:

var query = from a in context.a
            join b in context.bs on a.prikey equals b.forkey
            join c in context.cs on b.prikey equals c.forkey
            join d in context.ds on c.prikey equals d.forkey
            where ...
            select new string[] { a.arg, b.arg, c.arg, d.arg };

 string[] results = query.First();

I haven't tried creating arrays in LINQ to SQL... that may work, or you may need to go via an anonymous type and AsEnumerable as per the earlier part of my answer.

You should also think about what you want to happen if there are no results, or multiple results.

EDIT: Having seen the edited question, you really can treat multiple tables the same way as a single table. You'd use exactly the same code for handling the result, once it's been projected into an anonymous type:

var query = from a in context.as
            join b in context.bs on a.prikey equals b.forkey
            join c in context.cs on b.prikey equals c.forkey
            join d in context.ds on c.prikey equals d.forkey
            where ... 
            select new { a.Name, a.Age, b.Gender };

var result = query.First();
// Call ToString appropriately on each property, of course
string[] array = new string[] { result.Name, result.Age, result.Gender };