7

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.

1
  • as = plural of table a as t = query.First() string[] results = new string[] {t.firstval, t.secondval} ain't sure what do i fill the 'as' Commented Sep 29, 2011 at 6:18

2 Answers 2

7

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 };
Sign up to request clarification or add additional context in comments.

8 Comments

i would like to get data from the select statement of a.arg, a.arg, b.arg, c.arg, d.arg. i would actually do a as = plural of table a as t = query.First() string[] results = new string[] {t.firstval, t.secondval} ain't sure what do i fill the 'as'.
@vinz: Oh, I see... I think. It's somewhat unclear or at least unusual. Anyway, will edit.
@vinz: Okay, see the bottom of my answer now. Note that the where clauses in your sample query are pretty pointless, as at the moment you're just guaranteeing that you'll get a string array of { arg, arg, arg, arg} as you've filtered them on exactly that criterion... this is where giving realistic examples makes a big difference.
the where is to filter down the query with external factor. .First says it works with IQueryable. I'm working on a web form and not a MVC approach.
@vinz But your sample query filters on exact matches of the data you're selecting. Hopefully your real query is more sensible. Whether or not you're using MVC is pretty irrelevant.
|
0
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();

2 Comments

what do you mean by allStrings? a variable?
@vinz: Yes, projecting the results to a temp var allStrings

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.