LinQ query with multiple tables and data extraction

Question

I am making a query on the inner joins of 4 tables, and I need to extract the data and convert it to a string and put 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; 

Usually, if one table is used a as = plural of table a

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

I am missing a step to retrieve data.

Answer 1

It depends on what exactly you want to do with the data. At the moment, your code will not compile because it is trying to create an anonymous type with several properties, all called "arg", but I assume that you really have a more reasonable request.

Ultimately, the fact that you use multiple tables does not matter here - you get only one element of the result at a time: the fact that each element of the result contains data from several tables, neither here nor there in terms of how you address him.

Now I just noticed that you are saying that you want to “extract data and convert to string”. If possible, you should express this in your request. You can do this in the database, or you may need to force the final part of the execution locally, for example:

 // 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 this way, but this is probably the best approach, at least with the amount of information you gave us. If you can tell us more about how you are trying to combine data from several tables, we can help you more.

EDIT: Ok, now you have given us a little 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 have not tried creating arrays in LINQ to SQL ... that might work, or you might need to go through an anonymous type and AsEnumerable according to the previous part of my answer.

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

EDIT: after seeing the edited question, you can really handle multiple tables in the same way as a single table. You would use exactly the same code to process the result once it was 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 }; 

Answer 2

 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 a.arg == arg where b.arg == arg where c.arg == arg select new { allStrings = a.arg + a.arg + b.arg + c.arg + d.arg }; string[] results = query.ToArray();