How to Read Related Data Using LINQ/C# [one to many]

Question

I have two related tables with one to many relationship, I m trying to get a JSON response that populates ICollection<PatPar> PatPar the controller's code below reads fine as long as I have only one related record, once I have more than one related record I get a selection exception.

These are related Models

public class PatReg
    {
        [NotMapped]
        private Int64 _FileId;
        [Key, Display(Name = "File Id"), ScaffoldColumn(false), DatabaseGenerated(DatabaseGeneratedOption.None)]
        public Int64 FileId
        {
            get
            {
                return this._FileId;
            }
            set
            {
                this._FileId = value;
            }
        }
        [Required, Display(Name = "First Name")]
        public string FName { get; set; }

        public ICollection<PatPar> PatPar { get; set; }

    }


public class PatPar

    {
        [Key]
        public Int64 RecId { get; set; }
        [Display(Name = "Patient File Id"), Required]
        public Int64 FileId { set; get; }
        [Display(Name = "Partner File Id"), Required]
        public Int64 ParFileId { set; get; }
        [Display(Name = "Start Date"), DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true), Required]
        public DateTime SDate { set; get; }
        [Display(Name = "End Date"), DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public DateTime? EDate { set; get; }

    }

This is my API Controller

[HttpGet]
        public IEnumerable<PatReg> GetPatReg()
        {
            return _context.PatReg;
        }

        [HttpGet("{id}")]
        public async Task<IActionResult> GetPatReg([FromRoute] long id)
        {

            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var patReg = await _context.PatReg.SingleOrDefaultAsync(m => m.FileId == id);
            var parinfo = await _context.PatPar.SingleOrDefaultAsync(m => m.FileId == id); // I should select many records here 
            if (patReg == null)
            {
                return NotFound();
            }

            var DataRes = new {
                sData = patReg                
            };

            return Ok(DataRes);
        }

I know that I should use selectMany instead of using SingleOrDefaultAsync so I tried

IEnumerable<PatPar> parinfo = await _context.PatPar.SelectMany(m => m.FileId == id);

but the compiler is giving me errors, what is the way to do it?

Answer 1

Maybe you want actually this:

IEnumerable<PatPar> parinfo = _context.PatPar.Where(m => m.FileId == id);

SelectMany description:

Projects each element of a sequence to an IEnumerable and flattens the resulting sequences into one sequence.

So it is needed if you want to get IEnumerable<SomeSpecificField> as the result.

However, it is applicable only if PatPar had IEnumerable<SomeType> and you want to SelectMany<PatPar, SomeType>.

In your case there can be only Select

IEnumerable<Int64> parFileIds = _context.PatPar.Select(m => m.ParFileId );

Answer 2

You should be using Where instead of SelectMany:

var result = _context.PatPar.Where(m => m.FileId == id);

SelectMany is used to retrieve a collection from each item of the iterated collection. However your condition is to check if the FileId equals some id - the resulted type is a boolean. I assume what you are trying to do is return all the times that have that FileId.

To have it with the await:

var result = await _context.PatPar.Where(m => m.FileId == id).ToListAsync();