1

I am learning Document Databases, and we are using Marten/Linq in Visual Studio. The database is running through Postgres/PGAdmin. My database is football(not American) leagues, teams, players and managers. I am trying to construct queries based on multiple parameters. I have singular parameters down pretty well. 

List<Player> englishPlayers = finalDb.Query<Player>().Where(x => x.Nationality.Contains("English")).ToList();

This query would create a list of all players whose Nationality is set to "English" in the Document Database.

Player is my class/table with a Nationality "field". What I am trying to do is query based on multiple parameters. For instance, I have multiple "fields" that are either an int or bool. As an example, if I wanted to create a query to show all players of a certain Nationality with lifetimeGoals > 100, how would I accomplish this?

I have searched Google for about an hour, and read through the suggested similar questions, but most of those questions don't account for Marten being used.

I've tried breaking it down by individual queries first and then combine them, for instance: 

Player m4 = finalDb.Query<Player>().SelectMany(e => e.lifetimeGoals
                .Where(p => e.lifetimeGoals >= 0));

However, this throws an error stating

int does not contain a definition for where, and no extension method 'Where' accepting a first argument of type 'int'.

My terminology with this is a little off, but hopefully this is clear enough to find guidance.

Class for Player:

class Player
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Team { get; set; }
    public string prefPosition { get; set; }
    public string Nationality { get; set; }
    public int yearsAtCurrentClub { get; set; }
    public int lifetimeGoals { get; set; }
    public int domesticTitles { get; set; }
    public int europeanTitles { get; set; }
}//Class Player

Main class

static void Main(string[] args)
    {

        string connectionString = ConfigurationManager.ConnectionStrings
        ["FinalProjectDB"].ConnectionString;
        IDocumentStore store = DocumentStore.For(connectionString);

        using (var finalDb = store.OpenSession())
        {
        Player m4 = finalDb.Query<Player>().SelectMany(p => p.lifetimeGoals)
                    .Where(p => p.lifetimeGoals >= 0 && p.myString.Equals("valueToCheck"));

 Console.ReadLine();
 }
The_fatness
  • 37
  • 8

1 Answers1

1

You can't use .Where() on an integer. Instead use it like this: 

Player m4 = finalDb.Query<Player>().SelectMany(e => e.lifetimeGoals)
            .Where(p => p.lifetimeGoals >= 0);

the above query has a close bracket at the end of SelectMany allowing the Where clause to work with the intended query.

Due to adding a bracket on the end of the SelectMany there is then no needed to have an additional bracket at the end of the query.

Edit: You can simply add another clause to your .Where()

Player m4 = finalDb.Query<Player>().SelectMany(e => e.lifetimeGoals)
            .Where(p => p.lifetimeGoals >= 0 && p.myString.Equals("valueToCheck"));

You can use && for and or you can use || for or.

Second edit: I don't see why you are using .SelectMany(). You should be able to use your query like this:

Player m4 = finalDb.Query<Player>().Where(p => p.lifetimeGoals >= 0 && p.myString.Equals("valueToCheck")).FirstOrDefault();

Or use .ToList() when you want a list of players.

List<Player> players = finalDb.Query<Player>().Where(p => p.lifetimeGoals >= 0 && p.myString.Equals("valueToCheck")).ToList();
  • Ok, great. Thanks. What about combining a string check and int check in the same statement? I've figured out how to combine int checks based on your response, but when I try to incorporate a check for a string inside the same query it tells me – The_fatness Dec 16 '16 at 11:43
  • Sorry, not sure why it won't let me edit my other post the error is "'bool' does not contain a definition for "ToList' and no extension method 'ToList' accepting a first argument type of 'bool'" – The_fatness Dec 16 '16 at 11:45
  • Check my edit for your first comment. And how do you use `.ToList()` on your statement? –  Dec 16 '16 at 11:46
  • Because it's a document database and I have numerous values, I am adding everything that matches the query to a list and using a foreach loop to print each result line by line. – The_fatness Dec 16 '16 at 11:52
  • I asked you *How?* not *why?* –  Dec 16 '16 at 11:53
  • Hmm, I am not entirely sure how to answer that. I am just creating a list of the class/table(league, player, manager, team) and then running the query and assigning each value to the list. Upon trying your included suggestion, I receive the error "The type arguments for method 'Queryable.SelectMany' cannot be inferred from the usage". Using a List was the method suggested to me upon creating/learning this database. – The_fatness Dec 16 '16 at 12:02
  • I'm not really sure how your models look. Could you add the relevant models and the method to your original post? –  Dec 16 '16 at 12:05
  • Hopefully that's enough information, I am still learning but that's all the information tied to what I am working with. I have hundreds of lines creating the database information, but excluded that. – The_fatness Dec 16 '16 at 12:21
  • It does indeed, thank you. My process for trying to solve an issue is solve 1 issue then move to the next. I was able to query string without issue and then struggled with ints. Figured out selectmany worked with ints after finding other posts about that. Then moved on to combining them and couldn't work it out, this led me to create this post. Thanks a bunch for taking the time to assist. – The_fatness Dec 16 '16 at 12:30
  • No problem! `.SelectMany()` basically puts all values from different lists in 1 list. If you want to learn more about this function, read [this question/answer.](http://stackoverflow.com/questions/958949/difference-between-select-and-selectmany) –  Dec 16 '16 at 12:31